| 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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
