Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle SQL - Extracting clob value from XML with repeating nodes

greg_cDec 9 2012 — edited Dec 9 2012

Hi All,

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 (description). The following statement runs successfully when the node contains data of a non clob size:

SELECT
extractvalue (Value (wl), '*/description')
FROM test_xml
, TABLE (xmlsequence (extract (xml_data, '*/record'))) wl

but fails when description node contains a lot of data:

ORA-01706: user function result value was too large

I amended my query:

SELECT
extractvalue(Value (wl), '*/description').getClobVal()
FROM test_xml
, TABLE (xmlsequence (extract (xml_data, '*/record'))) wl

but this fails with:

ORA-22806: not an object or REF

Thanks in Advance

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 6 2013

Added on Dec 9 2012

#oracle, #performance-availability, #xml, #xml-db

4 comments

20,562 views