Oracle BI Publisher Blog

Another groooovy question today that I thought I had blogged but I was mistaken, that was SQL XML integration.
Let's assume I have XML docs or snippets stored in CLOB columns in a table. How can we get them out into the XML data from the Enterprise server or via SQL for that matter. Here's the XML in the column:

Column                  Contents
WAREHOUSE_ID            1001
WAREHOUSE_NAME          Colorado Big Hoose
WAREHOUSE_SPEC    <?xml version="1.0" ?> 
                  <Warehouse>
                   <Building>Owned</Building> 
                   <Area>25000</Area> 
                   <Docks>2</Docks> 
                   <DockType>Rear load</DockType> 
                   <WaterAccess>Y</WaterAccess> 
                   <RailAccess>N</RailAccess> 
                   <Parking>Street</Parking> 
                   <VClearance>10 ft</VClearance> 
                  </Warehouse>

We can use the getClobVal() function in our query thus:

select WAREHOUSE_ID as WAREHOUSE_ID
,      WAREHOUSE_NAME as WAREHOUSE_NAME 
,      WAREHOUSE_SPEC.getClobval() as WAREHOUSE_SPEC
from WAREHOUSES

this gets us

<ROWSET>
 <ROW>
  <WAREHOUSE_ID>1001</WAREHOUSE_ID>
  <WAREHOUSE_NAME>Colorado Big Hoose</WAREHOUSE_NAME>
  <WAREHOUSE_SPEC>
  <?xml version="1.0"?>
  <Warehouse>
   <Building>Owned</Building>
   <Area>25000</Area>
   <Docks>2</Docks>
   <DockType>Rear load</DockType>
   <WaterAccess>Y</WaterAccess>
   <RailAccess>N</RailAccess>
   <Parking>Street</Parking>
   <VClearance>10 ft</VClearance>
  </Warehouse>
 </WAREHOUSE_SPEC>
 </ROW>
 ...
</ROWSET>

See the embedded '<?xml version="1.0"?>' under WAREHOUSE_SPEC?
Thats not good, BIP is going to choke on that, so we need to remove it. I quickly scoured the Oracle docs on the getClobVal function but did not see anything. Being a lazeee geek I went for the obvious, a substring, does the trick nicely.

select WAREHOUSE_ID as WAREHOUSE_ID
,      WAREHOUSE_NAME as WAREHOUSE_NAME 
,      substr(WAREHOUSE_SPEC.getClobval(),22) as WAREHOUSE_SPEC
from WAREHOUSES

we now get:

<ROWSET>
 <ROW>
 <WAREHOUSE_ID>1001</WAREHOUSE_ID>
 <WAREHOUSE_NAME>Colorado Big Hoose</WAREHOUSE_NAME>
 <WAREHOUSE_SPEC>
  <Warehouse>
   <Building>Owned</Building>
   <Area>25000</Area>
   <Docks>2</Docks>
   <DockType>Rear load</DockType>
   <WaterAccess>Y</WaterAccess>
   <RailAccess>N</RailAccess>
   <Parking>Street</Parking>
   <VClearance>10 ft</VClearance>
  </Warehouse></WAREHOUSE_SPEC>
 </ROW>
 ...
</ROWSET>