I am attempting to run SQL on a table (called test_xml
with a column xml_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
You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.
Using the value() and exist() methods to retrieve values from an xml type column. The following example shows using both the value() method and the exist() method of the xml data type. The value() method is used to retrieve ProductModelID attribute values from the XML.
This was resolved via a response received on Oracle Forums:
See Forum Post
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With