4 Replies Latest reply: Dec 9, 2012 12:57 PM by greg_c RSS

    Oracle SQL - Extracting clob value from XML with repeating nodes

    greg_c
      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