0

I have one table with clob datatype that contains below xml.

  <?xml version='1.0' encoding='UTF-8'?>
<CustomNavigatorNode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:recordtype="urn:dataworld-com:recordtype" xmlns="urn:dataworld-com:schemas:custom_navigator_node" recordtype:state="modified">
 <NAME recordtype:datatype="Text">Data report Value</NAME>
 <KEY recordtype:datatype="Text">3abe649e-d183-4ef1-b394-644ecbf98da8</KEY>
 <PARENT_KEY recordtype:datatype="Text">629a0679-9c7f-4a4a-a293-114796bf9156</PARENT_KEY>
 <URL recordtype:datatype="Text" recordtype:dirty="true">https://ctx.alfa.ats:8443/BOE/</URL>
 <TAG recordtype:datatype="Text" xsi:nil="1" />
 <TYPE recordtype:datatype="Text">Url</TYPE>
 <ORDINAL recordtype:datatype="Integer">10000</ORDINAL>
 <NAME_OVERRIDE recordtype:datatype="Boolean">0</NAME_OVERRIDE>
</CustomNavigatorNode>

Table Structure:

CREATE TABLE test_clob (data_value  CLOB);
SELECT "Select Value from Name tag"
FROM test_clob

I need to pick the tag value from Node "NAME"

Desired Output require: Data report Value

Mohd Shoeb
  • 1
  • 3
  • I think what you need is in here https://stackoverflow.com/questions/28214725/how-to-select-a-particular-node-name-and-its-values-in-xml-using-oracle-sql-quer – thatjeffsmith May 11 '18 at 12:31
  • You are right, https://stackoverflow.com/questions/28214725/how-to-select-a-particular-node-name-and-its-values-in-xml-using-oracle-sql-quer or https://stackoverflow.com/questions/44515569/extracting-value-from-xml-clob-with-namespace-using-oracle-pl-sql contain useful explanation. – Srdjan May 20 '19 at 11:00

2 Answers2

0

Try this one:

SELECT dbms_xmlgen.convert(xmltype(data_value)
       .extract('//CustomNavigatorNode/NAME/text()')
       .getstringval(),1) as NAME
FROM test_clob;
bgtiban
  • 1
  • 1
0

You can try something like this:

select xmlquery('/CustomNavigatorNode/NAME/text()' 
   passing xmltype(data_value) 
   returning content) AS "Name"
from test_clob;

Take into account that maybe you should declare the namespace first (check for your specific case), for example:

select xmlquery('declare default element namespace "http://www.w3.org/2001/XMLSchema-instance"; /CustomNavigatorNode/NAME/text()' 
   passing xmltype(data_value) 
   returning content) AS "Name"
from test_clob;
Srdjan
  • 121
  • 5