Oracle Query to get data from more complex XML clob

Question

I have a fairly complex clob stuffed with xml in oracle that i would like to parse in SQL query like I have used in the past with Extract and ExtractValue.

Name Type
ATTRIBUTES CLOB

Simple query i used in past

    SELECT   EXTRACT(EXTRACT(xmltype.createxml(ATTRIBUTES),
'/Attributes/Map/entry[@key=""buildMapRule""]'),'/entry/@value').getStringVal() AS RULE
    FROM SPT_APPLICATION

which used to work for getting simple data from XML like this
<Attributes>
  <Map>
    <entry key="afterProvisioningRule"/>
    <entry key="beforeProvisioningRule"/>
    <entry key="buildMapRule" value="Build Map Rule - ALM"/>
  </Map>
</Attributes>

But now i have something like this
<Attributes>
<Map>
   <entry key="buildMapRule" value="Build Map Rule - ALM"/>
   <entry key="compositeDefinition"/>
   <entry key="disableOrderingCheck">
     <value>
       <Boolean>true</Boolean>
     </value>
   </entry>
   <entry key="group.mergeRows">
     <value>
       <Boolean></Boolean>
     </value>
   </entry>
   <entry key="group.useExecuteQuery">
     <value>
       <Boolean></Boolean>
     </value>
   </entry>
   <entry key="myColumns">
     <value>
       <List>
         <String>Account Index</String>
         <String>Account Index2</String>
         <String>Account Index3</String>
       </List>
     </value>
   </entry>
</Map>
</Attributes>

I want to extract the data of this xml in a usable format using oracle flavored sql.....something like this but i am willing to listen if it is another format too...so basically flattened format....I am thinking xmlTable will help me out but can that be done dynamically with out knowing all the columns ( XML complex Parsing ) this link obviously knew columns and types

entryKey entryValue subComponentName subComponentValue

I will be happy to help you and probably am capable to do so, but I do not understand the question after reading it multiple times. Can you please rephrase? — Guido Leenders, Jan 08 '14 at 06:14
I question where the column names are determined? Inside some other software? Is there any way to know which columns to expect? — Patrick Hofman, Jan 08 '14 at 14:48

score 0 · Answer 1 · edited May 23 '17 at 12:18

Look into Oracle xmltype: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/t_xml.htm#ARPLS369

Example snippets:

procedure myProc( xml_in in clob ) is
    myXML xmltype;
begin
    myXML := xmltype.createxml( xml_in );
    /*
    from here you can then use functions like:
    extract()
    xmlsequence()
    etc... look through the docs, there's plenty of functions to use,
    they should satisfy your needs
    */
end;

https://community.oracle.com/thread/2378521?tstart=0

Oracle extract values from xmltype

Oracle Query to get data from more complex XML clob

1 Answers1