Going out Clobbing?

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>
Valid XML for BIP to consume and we can build a template against it.
Now to go clubbin, techno, techno, techno ... yeah right Dexter, how about a nice cup o tea and a slice o cake ... more your style these daze!

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today