Query XML CLOB column to get sub XML in the column

Question

I have a CLOB column in my table which contains an XML. I want to fetch the xml after a specific tag to its ending tag i.e

Full XML in CLOB column

<ParentTag>
 <Location>ABC XYZ ....</Location>
 <Person>
  <Name>Mohsin</Name>
  <Age>23</Age>
 </Person>
</ParentTag>

What I am trying to fetch is something like this:

<Person>
  <Name>Mohsin</Name>
  <Age>23</Age>
</Person>

I have tried to use dbms_lob.substr and dbms_lob.getlength but that doesnt help as the sub XML may contain the <Person> tag starting at different bytes in different scenarios.

Any help would be greatly appreciated. Thanks

Alex Poole · Accepted Answer · 2016-05-09 09:53:44Z

4

Don't try to parse the node out yourself with substrings. Oracle has extensive XML support built-in. You can do this with an XMLQuery:

select xmlquery('/ParentTag/Person' passing xmltype(xml_clob) returning content)
  as xml_value
from your_table;

XML_VALUE                                                                      
--------------------------------------------------------------------------------
<Person><Name>Mohsin</Name><Age>23</Age></Person>

If your XML document (in the CLOB) can have multiple person nodes then you can use XMLTable instead to extract them all.

And if you want it to be a formatted string matching what you've shown, rather than an XML document, you can use an XMLSerialize wrapper call:

select xmlserialize(content
  xmlquery('/ParentTag/Person' passing xmltype(xml_clob) returning content)
    as varchar2(100) indent size=2) as string_value
from your_table;

STRING_VALUE                                                                   
--------------------------------------------------------------------------------
<Person>                                                                        
  <Name>Mohsin</Name>                                                           
  <Age>23</Age>                                                                 
</Person>

Following up a comment, if you have anamespace you can declare that as part of the XPath:

select xmlquery('declare namespace NS4 = "http://soa.comptel.com/2011/02/instantlink"; /ParentTag/NS4:Person'
  passing xmltype(prov_request) returning content) as xml_value
from your_table;

select xmlserialize(content
  xmlquery('declare namespace NS4 = "http://soa.comptel.com/2011/02/instantlink"; /ParentTag/NS4:Person'
      passing xmltype(prov_request) returning content)
    as varchar2(150) indent size=2) as string_value
from your_table;

The extracted Person node will still have that namespace information though:

STRING_VALUE                                                                   
--------------------------------------------------------------------------------
<NS4:Person xmlns:NS4="http://soa.comptel.com/2011/02/instantlink">             
  <NS4:Name>Mohsin</NS4:Name>                                                   
  <NS4:Age>23</NS4:Age>                                                         
</NS4:Person>

edited May 9, 2016 at 9:53

answered May 6, 2016 at 17:23

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jonathan white Over a year ago

Hi alex, Thank you very much for the answer. Pardon me to miss out on namespaces. I have namespace on the <Person> tag.

jonathan white Over a year ago

like this:

code <ParentTag>  <Location>ABC XYZ ....</Location>  <NS4:Person xmlns:NS4="http://soa.comptel.com/2011/02/instantlink">   <NS4:Name>Mohsin</NS4:Name>   <NS4:Age>23</NS4:Age>  </NS4:Person> </ParentTag>

I have tried using code passing xmltype(prov_request) returning content) but i am unable to get the desired result.

Alex Poole Over a year ago

@user3081780 - updated with a version that handles the namespace.

Collectives™ on Stack Overflow

Query XML CLOB column to get sub XML in the column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related