title: Oracle Query to get data from more complex XML clob
body: <p>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.</p>
  
  <p><strong>Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Type</strong><br/>
  ATTRIBUTES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CLOB</p>
  
  <p><strong>Simple query i used in past</strong><br/></p>
  
  <pre><code>    SELECT   EXTRACT(EXTRACT(xmltype.createxml(ATTRIBUTES),
  '/Attributes/Map/entry[@key=""buildMapRule""]'),'/entry/@value').getStringVal() AS RULE
      FROM SPT_APPLICATION
  </code></pre>
  
  <p><strong>which used to work for getting simple data from XML like this</strong><br/>
  <code>&lt;Attributes&gt;</code><br/>
  &nbsp;&nbsp;<code>&lt;Map&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;entry key="afterProvisioningRule"/&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;entry key="beforeProvisioningRule"/&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;entry key="buildMapRule" value="Build Map Rule - ALM"/&gt;</code><br/>
  &nbsp;&nbsp;<code>&lt;/Map&gt;</code><br/>
  <code>&lt;/Attributes&gt;</code><br/></p>
  
  <p><strong>But now i have something like this</strong><br/>
  <code>&lt;Attributes&gt;</code><br/>
  &nbsp;<code>&lt;Map&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;entry key="buildMapRule" value="Build Map Rule - ALM"/&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;entry key="compositeDefinition"/&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;entry key="disableOrderingCheck"&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;value&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;Boolean&gt;true&lt;/Boolean&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;/value&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;/entry&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;entry key="group.mergeRows"&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;value&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;Boolean&gt;&lt;/Boolean&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;/value&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;/entry&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;entry key="group.useExecuteQuery"&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;value&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;Boolean&gt;&lt;/Boolean&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;/value&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;/entry&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;entry key="myColumns"&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;value&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;List&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;String&gt;Account Index&lt;/String&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;String&gt;Account Index2&lt;/String&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;String&gt;Account Index3&lt;/String&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;/List&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>&lt;/value&gt;</code><br/>
  &nbsp;&nbsp;&nbsp;<code>&lt;/entry&gt;</code><br/>
  &nbsp;<code>&lt;/Map&gt;</code><br/>
  <code>&lt;/Attributes&gt;</code><br/></p>
  
  <p>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 ( <a href="https://stackoverflow.com/questions/12883586/xml-complex-parsing">XML complex Parsing</a> ) this link obviously knew columns and types</p>
  
  <p>entryKey&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;entryValue&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;subComponentName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;subComponentValue</p>