CODEWITHSUNDEEP
sqlregexxmloracle-databaseplsql
Ork
Ork

Reputation: 61

Split Clob containing XML

I have a table containing, in a clob column, a value like this: <root><node><a>text1a</a><b>text1b</b></node><node><a>text2a</a><b>text2b</b></node></root>

Using PL/SQL I need to query it and obtain this output in two rows:

<node><a>text1a</a><b>text1b</b></node>
<node><a>text2a</a><b>text2b</b></node>

It could be more the 4000 chars each one. Tag must be included in the output.

Upvotes: 1

Views: 741

Answers (2)

Amir Kadyrov
Amir Kadyrov

Reputation: 1288

Convert clob to xmltype and use xmltable to parse it:

with s as (select '<root><node><a>text1a</a><b>text1b</b></node><node><a>text2a</a><b>text2b</b></node></root>' c from dual)
select x.node node_xml, x.node.getclobval() node_clob
from s,
xmltable(
'/root/node'
passing xmltype(s.c)
columns
node xmltype path '.'
) x;

NODE_XML                                   NODE_CLOB                                 
------------------------------------------ ------------------------------------------
<node><a>text1a</a><b>text1b</b></node>    <node><a>text1a</a><b>text1b</b></node>   
<node><a>text2a</a><b>text2b</b></node>    <node><a>text2a</a><b>text2b</b></node>

Upvotes: 2

Saad Ahmad
Saad Ahmad

Reputation: 403

Original data is not in varchar; it is in some LOB. If you are ok with returning 2 lobs, look up PLSQL table functions (to put a function in from clause) and utilizing dbms_lob package return 2 rows where each row is a LOB as well.

If you want to return it as varchar data, then you have the 4000 limit. All you can do is return in multiple rows of 4000 and combine them all in your client software.

You can see this link where there is a solution to split blob into 4000 byte strings. https://medium.com/@thesaadahmad/a-blobs-journey-from-the-database-to-the-browser-98884261e137

Upvotes: -1

Related Questions