How to find Node Value From Clob XML in Oracle

Question

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

Answer 1

I'm not sure, but... you can try this.

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

Answer 2

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;