XQuery changes <> to '&lt' and '&gt' during update node with CDATA

Question

I've got a problem with updating a large XML file that is placed in an oracle database.

This XML describes database structure, and i have to update many nodes. The problem that I have, showed up when I had to update nodes with <![CDATA[...]]> in them. The query that I commonly used, looked like this:

 UPDATE XML_TABLE set XML_TABLE.xml =
  (select XMLQuery('copy $tmp := . modify
     (for $i in $tmp/MODEL/ERD/ENTITIES/ENTITY[upper-case(NAME) = "SOME_TABLE"]NOTES/text()  
      return replace value of node $i with $p0) 
            return $tmp'
           PASSING x.xml, '<![CDATA[ new text value ]]>' as "p0" RETURNING CONTENT) FROM XML_TABLE x )

The reason that I have chosen this kind of query is because I can make it update many nodes at a time:

select xmlquery(' copy $tmp := . modify 
                  ((for $j in $tmp/MOD (...)return insert node $p1 as last into $j),
                   (for $j in $tmp/MODEL(...)return insert node $p2 as last into $j),
                   (for $j in $tmp/MODEL(...)return insert node $p3 as last into $j))
                   return $tmp'
                   passing x.xml, 'whatever1' as "p1",
                                  'something1' as "p2",
                                  'sth2' as "p3"                    
                   returning content
                   )
From XML_TABLE x

Unfortunately, I can't update nodes with <![CDATA[...]]>. The '<' and '>' signs are replaced with '<' and '>'. I can't encapsulate CDATA in XMLTYPE: XMLTYPE('<![CDATA[...]]>), because I get an error.

I could use deprecated function UPDATEXML(), but I need the upper-case() function that is not supported in XPath 1.0, and the translate() method is ugly, and I don't know how I would use it in this case. And of course, I got no guarantee that it would not translate < > to < >.

Do you know any way to help me with my problem?

OK, I have used a UPDATEXML() function which worked for me. It does not change < > to < >, but do not support any upper-case() function (only translate()). But I had a capability to slightly rebuild my program and now, I do not have to make a strings comparision. However, this is not an answer to question, so I will leave it as unresolved.

Answer 1

CDATA does not replace anything. It is just an XML construct that let you write plain text, without having to escape the markup. This is only syntactic sugar, and is always equivalent to writing its content as text with the proper escaping. For instance:

<p><![CDATA[Use <p>Hello.</p> to write a paragraph.]]></p>

is always equivalent to:

<p>Use &lt;p>Hello.&lt;/p> to write a paragraph.</p>

This can be useful sometimes, when you write an XML document by hand (like an article) containing a lot of markup in the text (like an article about XML, containing XML examples in the text).

Once parsed, CDATA is not "recorded" in any way. Once passed to an XQuery, both examples above will produce the exact same result (most likely, containing < escaped characters, if the text is copied to the output).