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>
Advertisements

2 Comments »

  1. Is there a way to do this without the ROW element added ?? I used your example (works great), but I can’t have the ROW element.

    thanks jim

    Comment by Jim — December 18, 2007 @ 6:21 pm

  2. Jim,
    select replace(
    ((dburitype.createuri(‘/QSN_APP/TX_PORTAL/ROW[PORTAL_NAME=”MORTGAGE”]’)).getclob()),
    ‘ROW>’,’ABC>’) mydata from dual;

    Comment by Michael Moore — January 22, 2008 @ 11:44 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: