OraFAQ Forum: SQL & PL/SQL » Extract clob xml data to get node value

Home » SQL & PL/SQL » SQL & PL/SQL » Extract clob xml data to get node value (Oracle RDBMS, 12.1, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Extract clob xml data to get node value [message #676085]

Mon, 13 May 2019 14:59

unna
Messages: 12
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!

Report message to a moderator

Re: Extract clob xml data to get node value [message #676086 is a reply to message #676085]

Mon, 13 May 2019 15:05

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Extract clob xml data to get node value [message #676087 is a reply to message #676085]

Mon, 13 May 2019 15:15

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

_jum
Messages: 577
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