Retrieving XML data from clob greater than 4,000 characters

Question

I have the following Oracle query that returns data from a clob column (mytable.personalization_data). It works fine if the clob value is less than 4,00 characters but if it is more, I get an "ORA-01706: user function result value was too large" error message.

Here is the sql:

select name_str, value_str, order_str
from 
(
SELECT
XMLTYPE(EXTRACTVALUE(XMLTYPE(personalization_data), '/personalizations/personalization[1]/data')) persData 
FROM my_table
),
XMLTable('/Accessories/Personalization/PersonalizationItems'
    PASSING persData
    COLUMNS
        name_str  varchar2(100)  PATH 'DisplayName',
        value_str varchar2(2000) PATH 'Value',
        order_str varchar2(10)   PATH 'SortOrder'
);

Sample XML:

<personalizations>
    <personalization>
        <data>
            <![CDATA[
            <Accessories>
                <AccessoryId>1234567</AccessoryId>
                <Personalization>
                    <PersonalizationItems>
                        <SortOrder>1</SortOrder>
                        <DisplayName>Last Name</DisplayName>
                        <Value>Veekoff</Value>
                    </PersonalizationItems>
                    <PersonalizationItems>
                        <SortOrder>2</SortOrder>
                        <DisplayName>First Name</DisplayName>
                        <Value>Ivana</Value>
                    </PersonalizationItems>
                </Personalization>
            </Accessories>
            ]]>
        </data>
    </personalization>
</personalizations>

Could someone please offer suggestions?

Answer 1

So... the problem is that EXTRACTVALUE only returns VARCHAR2. And you're using it to remove the <![CDATA[ stuff from the data node, so you can parse it a second time as XML. As it turns out, most of the ways that Oracle lets you pretty-print XML or alter a clob in-place in SQL return VARCHAR2.

I think I came up with something that should work, using xpath functions to get rid of the CDATA wrapper, and unescaping the result. It's a little awkward, and I'm sure there must be a better way to do it, but I couldn't find it.

select name_str, value_str, order_str
from 
(
SELECT XMLTYPE(DBMS_XMLGEN.CONVERT(
    XMLQUERY('/personalizations/personalization[1]/data/substring(text(),9,string-length(text())-12)' passing XMLTYPE(personalization_data) returning content).getClobVal()
    , 1)) persData 
FROM my_table
),
XMLTable('/Accessories/Personalization/PersonalizationItems'
    PASSING persData
    COLUMNS
        name_str  varchar2(100)  PATH 'DisplayName',
        value_str varchar2(2000) PATH 'Value',
        order_str varchar2(10)   PATH 'SortOrder'
);

I guess plain substr can handle clobs, but it's not much of an improvement.

select name_str, value_str, order_str
from 
(
SELECT XMLTYPE(substr( pData, 10, length(pData)-12)) persData 
from (select 
    XMLQUERY('/personalizations/personalization[1]/data/text()' passing XMLTYPE(personalization_data) returning content).getClobVal() as pData
    FROM my_table)
),
XMLTable('/Accessories/Personalization/PersonalizationItems'
    PASSING persData
    COLUMNS
        name_str  varchar2(100)  PATH 'DisplayName',
        value_str varchar2(2000) PATH 'Value',
        order_str varchar2(10)   PATH 'SortOrder'
);