PL/SQL

February 25, 2007

Single table row to XML CLOB using DBURITYPE

Filed under: dburitype, example, PL/SQL, pl/sql code — Michael Moore @ 5:52 pm
set long 2200

-- PORTAL_NAME is a unique column of table TX_PORTAL in the QSN_APP schema

SQL> select (dburitype.createuri('/QSN_APP/TX_PORTAL/ROW[PORTAL_NAME="MORTGAGE"]')).getclob() mydata from dual;

MYDATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
 <ROW>
  <PORTAL_KEY>1000</PORTAL_KEY>
  <PORTAL_NAME>MORTGAGE</PORTAL_NAME>
  <DESCRIPTION>Mortgage Portal</DESCRIPTION>
  <CREATEDDATE>17-NOV-04</CREATEDDATE>
  <CREATEDBY2TUSER>1250</CREATEDBY2TUSER>
  <UPDATEDDATE>20-OCT-05</UPDATEDDATE>
  <UPDATEDBY2TUSER>1250</UPDATEDBY2TUSER>
  <SUPPRESS_COMM_WHEN_NO_VENDORS>N</SUPPRESS_COMM_WHEN_NO_VENDORS>
  <TRACE_MATCH_PROCESS>Y</TRACE_MATCH_PROCESS>
  <TRACE_TABLE_MAX_ROWS>10000</TRACE_TABLE_MAX_ROWS>
  <EMAIL_ERRORS_TO>somebody@somewhere.com</EMAIL_ERRORS_TO>
  <FRONT_END_SPLIT>N</FRONT_END_SPLIT>
  <CATEGORY2TCODE>9261</CATEGORY2TCODE>
 </ROW>

Create a free website or blog at WordPress.com.