Home » SQL & PL/SQL » SQL & PL/SQL » Extract clob xml data to get node value (Oracle RDBMS, 12.1, Windows)
Extract clob xml data to get node value [message #676085] Mon, 13 May 2019 14:59 Go to next message
unna
Messages: 8
Registered: October 2018
Junior Member
I have these xml data in my oracle 12c database:

select testclob from traptabclob;
Result:

<?xml version="1.0" encoding="UTF-8"?>
<Values version="2.0">
<record name="getEntities" javaclass="com.wm.util.Values">
<record name="transactionInformation" javaclass="com.wm.util.Values">
<value name="transactionStatus">01</value>
</record>
<record name="listOfEntities" javaclass="com.wm.util.Values">
<array name="entity" type="record" depth="1">
<record javaclass="com.wm.util.Values">
<record name="coreData" javaclass="com.wm.util.Values">
<record name="Information" javaclass="com.wm.util.Values">
<record name="OrdInformation" javaclass="com.wm.util.Values">
<value name="IdentificationType">ID</value>
<value name="IdentificationNumber">123456</value>
</record>
<record name="hqParent" javaclass="com.wm.util.Values">
<value name="IdentificationType">Name</value>
<value name="IdentificationNumber">Jone Doe</value>
</record>
</record>
</record>
</record>
</array>
</record>
</record>
<record name="soapHeaders" javaclass="com.wm.util.Values">
</record>
</Values>

I need to retrieve value (Jone Doe) for IdentificationNumber, I tried this:
SELECT EXTRACTVALUE(xmltype(testclob), '/record/record/array/record/record/record/record/value[@name="IdentificationNumber"]')
FROM traptabclob;

But I got null value returned. Any advice on how to retrieve the value? Any help will be appreciated!
Re: Extract clob xml data to get node value [message #676086 is a reply to message #676085] Mon, 13 May 2019 15:05 Go to previous messageGo to next message
BlackSwan
Messages: 26533
Registered: January 2009
Location: SoCal
Senior Member
Why do you REFUSE to follow Posting Guidelines?

http://www.orafaq.com/forum/mv/msg/205218/672763/#msg_672763
Re: Extract clob xml data to get node value [message #676087 is a reply to message #676085] Mon, 13 May 2019 15:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You missed "Values" in your path.
This won't work as EXTRACTVALUE can return value for only one node and you have several ones for this path.

Now you have several nodes for this path and attribute, so you have to add a condition to specify which one you want:
SQL> col value format a20
SQL> with
  2    data as (
  3      select xmltype('<?xml version="1.0" encoding="UTF-8"?>
  4  <Values version="2.0">
  5  <record name="getEntities" javaclass="com.wm.util.Values">
  6  <record name="transactionInformation" javaclass="com.wm.util.Values">
  7  <value name="transactionStatus">01</value>
  8  </record>
  9  <record name="listOfEntities" javaclass="com.wm.util.Values">
 10  <array name="entity" type="record" depth="1">
 11  <record javaclass="com.wm.util.Values">
 12  <record name="coreData" javaclass="com.wm.util.Values">
 13  <record name="Information" javaclass="com.wm.util.Values">
 14  <record name="OrdInformation" javaclass="com.wm.util.Values">
 15  <value name="IdentificationType">ID</value>
 16  <value name="IdentificationNumber">123456</value>
 17  </record>
 18  <record name="hqParent" javaclass="com.wm.util.Values">
 19  <value name="IdentificationType">Name</value>
 20  <value name="IdentificationNumber">Jone Doe</value>
 21  </record>
 22  </record>
 23  </record>
 24  </record>
 25  </array>
 26  </record>
 27  </record>
 28  <record name="soapHeaders" javaclass="com.wm.util.Values">
 29  </record>
 30  </Values>') val from dual
 31    )
 32  select position, EXTRACTVALUE(column_value,'/value') value
 33  from data,
 34       xmltable('/Values/record/record/array/record/record/record/record/value'
 35                passing val
 36                columns position for ordinality)
 37  where EXTRACTVALUE(column_value, '/value/@name') = 'IdentificationNumber'
 38  /
  POSITION VALUE
---------- --------------------
         2 123456
         4 Jone Doe

2 rows selected.

[Updated on: Mon, 13 May 2019 15:18]

Report message to a moderator

Re: Extract clob xml data to get node value [message #676088 is a reply to message #676087] Tue, 14 May 2019 01:41 Go to previous messageGo to next message
_jum
Messages: 545
Registered: February 2008
Senior Member
Because EXTRACTVALUE function is deprecated in ORACLE 12, you could use XMLTable instead:
with data as
 (select xmltype(
 '<?xml version="1.0" encoding="UTF-8"?>
  <Values version="2.0">
    <record name="getEntities" javaclass="com.wm.util.Values">
      <record name="transactionInformation" javaclass="com.wm.util.Values">
        <value name="transactionStatus">01</value>
      </record>
      <record name="listOfEntities" javaclass="com.wm.util.Values">
        <array name="entity" type="record" depth="1">
          <record javaclass="com.wm.util.Values">
            <record name="coreData" javaclass="com.wm.util.Values">
              <record name="Information" javaclass="com.wm.util.Values">
                <record name="OrdInformation" javaclass="com.wm.util.Values">
                  <value name="IdentificationType">ID</value>
                  <value name="IdentificationNumber">123456</value>
                </record>
                <record name="hqParent" javaclass="com.wm.util.Values">
                  <value name="IdentificationType">Name</value>
                  <value name="IdentificationNumber">Jone Doe</value>
                </record>
              </record>
            </record>
          </record>
        </array>
      </record>
    </record>
    <record name="soapHeaders" javaclass="com.wm.util.Values">
    </record>
  </Values>') val from dual)
   select xt.*
   from data,
        xmltable('/Values/record/record/array/record/record/record/record/value[@name="IdentificationNumber"]'
          passing val
          columns position for ordinality
                , vname  VARCHAR2(100) PATH '/value' ) xt;

POSITION VNAME
--------------------
1 123456
2 Jone Doe

[Updated on: Tue, 14 May 2019 01:43]

Report message to a moderator

Re: Extract clob xml data to get node value [message #676089 is a reply to message #676088] Tue, 14 May 2019 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Depreciated does not mean it does not work. Wink
But sure I could/should add the desired field and test in the XMLTABLE specification. Smile

[Edit: missing word]

[Updated on: Wed, 15 May 2019 02:53]

Report message to a moderator

Re: Extract clob xml data to get node value [message #676107 is a reply to message #676089] Tue, 14 May 2019 15:57 Go to previous message
unna
Messages: 8
Registered: October 2018
Junior Member
Thanks all for your replies and pointing me to the right direction. I am able to move forward.

Thanks again!

Previous Topic: Why does my SQL not work?
Next Topic: Update in EXECUTE IMMEDIATE
Goto Forum:
  


Current Time: Sat May 25 11:56:38 CDT 2019