Oracle Query to retrieve data from more complex XML clob

Question

I have a rather complicated clob packed with xml in oracle, which I would like to parse in a SQL query, as I used in the past with Extract and ExtractValue.

Name Type CLOB ATTRIBUTES

The simple query I used in the past

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

that was used to get simple data from XML, such as
<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>

xml , oracle flavored sql..... - , , ... .... , xmlTable , , (XML- ),

entryKey     entryValue     subComponentName     subComponentValue

Answer 1

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

:

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 plenty of functions to use,
    they should satisfy your needs
    */
end;

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

Oracle xmltype