This discussion is archived
4 Replies Latest reply: Dec 9, 2012 10:57 AM by greg_c RSS

Oracle SQL - Extracting clob value from XML with repeating nodes

greg_c Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points