How to not escape special chars when updating XML in oracle SQL

Question

I have a problem trying to update xmlType values in oracle. I need to modify the xml looking similar to the following:

<a>
  <b>Something to change here</b>
  <c>Here is some narrative containing weirdly escaped &lt;tags>\&lt;/tags> </c>
</a>

What I want to achieve is to modify <b/> without modifying <c/>

Unfortunately following modifyXml:

select 
  updatexml(XML_TO_MODIFY, '/a/b/text()', 'NewValue')
from dual;

returns this:

<a>
  <b>NewValue</b>
  <c>Here is some narrative containing weirdly escaped &lt;tags&gt;&lt;/tags&gt; </c>
</a>

as you can see, the '>' had been escaped.

Same happens for xmlQuery (the new non-deprecated version of updateXml):

select /*+ no_xml_query_rewrite */
      xmlquery(
        'copy $d := .
         modify (
           for $i in $d/a
           return replace value of node $i/b with ''nana''
         )
         return $d'
        passing t.xml_data
         returning content
       ) as updated_doc
from (select xmlType('<a>
      <b>Something to change here</b>
      <c>Here is some narrative containing weirdly escaped \&lt;tags>\&lt;/tags> </c>
    </a>') as xml_data from dual) t
;

Also when using xmlTransform I will get the same result. I tried to use the

disable-output-escaping="yes"

But it did the opposite - it unescaped the &lt; :

select XMLTransform(
    xmlType('<a>
      <b>Something to change here</b>
      <c>Here is some narrative containing weirdly escaped \&lt;tags>\&lt;/tags> </c>
    </a>'),
    XMLType(
'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:template match="/a/b">
                <b>
                <xsl:value-of select="text()"/>
                </b>
        </xsl:template>

        <xsl:template match="/a/c">
                <c>
                <xsl:value-of select="text()" disable-output-escaping="yes"/>
                </c>
        </xsl:template>
</xsl:stylesheet>'))
from dual;

returned:

<a>
  <b>NewValue</b>
  <c>Here is some narrative containing weirdly escaped <tags></tags> </c>
</a>

Any suggestions? Two things you need to know:

1. I cannot modify the initial format - it comes to me in this way and I need to preserve it.
2. The original message is so big, that changing the message to string and back (to use regexps as workaround) will not do the trick.

Answer 1

The root of your issue seems to be that your original XML value for node C is not valid XML if it contains the > within the value instead of >, and not inside a CDATA section (also What does <![CDATA[]]> in XML mean?).

The string value of:

Here is some narrative containing weirdly escaped <tags>\</tags>

in XML format should really be

<c>Here is some narrative containing weirdly escaped &amp;lt;tags&gt;\&amp;lt;/tags&gt;</c>

OR

<c><![CDATA[Here is some narrative containing weirdly escaped &lt;tags>\&lt;/tags>]]></c>

I would either request that the XML be corrected at the source, or implement some method to sanitize the inputs yourself, such as wrapping the <c> node values in <![CDATA[]]>. If you need to save the exact original value, and the messages are large, then the best I can think of is the store duplicate copies, with the original value as string, and store the "sanitized" value as XML data type.

Answer 2

In the end we managed to do this with the help of java. By:

1. reading the xml as a clob
2. modifying it in java
3. storing it back in the database using java.sql.Connection (for some reason, if we used JdbcTemplate, it complained about casting to Long, which was indication that string was over 4000 bytes (talking about clean errors, all hail Oracle) and using CLOB Type didn't really help. I guess it's a different story though)

When storing the data, oracle does not perform any magic, only updates tend to modify escape characters.

Possibly not an answer for everyone, but a nice workaround if you stumble upon same problem as we did.