Oracle SQL - Extracting clob value from XML with repeating nodes
15,749
This was resolved via a response received on Oracle Forums:
From Oracle release 11.2.0.2:
SELECT x.*
FROM test_xml t
, XMLTable(
'/*/record'
passing t.xml_data
columns
test_3 clob path 'test_3'
) x
;
My database version is 10.2.0.4 hence the following 'trick' is required:
SELECT dbms_xmlgen.convert(x.test_3.getClobVal(), 1) as test_3
FROM test_xml t
, XMLTable(
'/*/record'
passing t.xml_data
columns
test_3 xmltype path 'test_3/text()'
) x
;
Thanks go to odie_63 for this
Author by
Greg Cox
Updated on June 16, 2022Comments
-
Greg Cox almost 2 years
I am attempting to run SQL on a table (called
test_xml
with a columnxml_data
[data type xmltype]). The column contains xml with repeating nodes (test_3
). The following statement runs successfully when the node contains data of a non clob size:SELECT extractvalue (Value (wl), '*/test_3') FROM test_xml , TABLE (xmlsequence (extract (xml_data, '*/record'))) wl
but fails when test_3 node contains a lot of data:
ORA-01706: user function result value was too large
I amended my query:
SELECT extractvalue(Value (wl), '*/test_3').getClobVal() FROM test_xml , TABLE (xmlsequence (extract (xml_data, '*/record'))) wl
but this fails with:
ORA-22806: not an object or REF