Using SQL to extract values from xml CLOB

Question

I'm currently trying to figure out on how to extract some values using plsql from a xml CLOB value that is stored in my database.

My CLOB value looks like the following:

<map>
  <entry>
    <string>HeaderOne</string>
    <string>
        <linked-hash-map>
            <entry>
                <string>ID</string>
                <string>81</string>
            </entry>
            <entry>
                <string>Name</string>
                <string>John</string>
            </entry>
            <entry>
                <string>SecondName</string>
                <string>Smith</string>
            </entry>
            <entry>
                <string>Age</string>
                <string>15</string>
            </entry>
        </linked-hash-map>
    </string>
  </entry>
  <entry>
    <string>HeaderTwo</string>
    <string>
        <linked-hash-map>
            <entry>
                <string>ID</string>
                <string>81</string>
            </entry>
            <entry>
                <string>ZIP</string>
                <string>99999</string>
            </entry>
            <entry>
                <string>Gender</string>
                <string>M</string>
            </entry>
        </linked-hash-map>
    </string>
  </entry>
</map>

I tried using the EXTRAC(xmltype(myclob) method as described here: Extract data from XML Clob using SQL from Oracle Database

However this won't work in my case because the <string> tag doesn't include an identifier suchs as 'name' or 'key', but instead lists another <string> entry above the actual value. I.e. <string>ID</string> is the parameter as one would normally use <string name='ID'>81</string> where '81' in this case is the actual value I want to be extracted.

The parameters can be listed in any order in the xml, however I know the name from the parameter I wish to extract. So my question is, is there a way to extract for example the value from the <string>Name</string> entry (in this case 'John', however this can be any value)

So the order of `` elements inside an `` would decide whether it is the attribute name or value? — ruudvan, Mar 31 '16 at 15:12

ruudvan · Accepted Answer · 2016-03-31T18:24:36.373

You could look for ways to PIVOT the output if you like, but here's a start to how you would read such an XML -

For simplicity, assuming that the XML is inside a variable named my_xml of the type XMLTYPE.

SELECT header_string, attribute_name, attribute_value
  FROM (WITH xm AS (SELECT my_xml AS x FROM DUAL)
        SELECT header_string, linked_hash_map
          FROM xm,
               XMLTABLE (
                  '//map/entry'
                  PASSING xm.x
                  COLUMNS header_string VARCHAR2 (100) PATH 'string[1]',
                          linked_hash_map XMLTYPE PATH 'string[2]/linked-hash-map'))
       l,
       XMLTABLE (
          '//linked-hash-map/entry'
          PASSING l.linked_hash_map
          COLUMNS attribute_name VARCHAR2 (100) PATH 'string[1]',
                  attribute_value VARCHAR2 (100) PATH 'string[2]');

I should mention that if possible, look for ways to improve the XML schema. From the question, it seems you already know that a better way is to use a key value pair where the key name isn't the actual data but the property of the element (if not the element name itself).

Also, don't use EXTRACT or EXTRACTVALUE, those functions have been deprecated by Oracle. Use XMLTABLE or XMLQUERY instead.

Thanks for the answer, I will check it out later and report back on the results! — SQLNewbie, Apr 01 '16 at 05:41

Using SQL to extract values from xml CLOB

1 Answers1