How to avoid special characters when updating XML in oracle SQL
I have a problem updating xmlType values in oracle. I need to change the xml similar to the following:
<a>
<b>Something to change here</b>
<c>Here is some narrative containing weirdly escaped <tags>\</tags> </c>
</a>
I want to achieve to change <b / "> without changing <c /">
Unfortunately the following modifier:
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 <tags></tags> </c>
</a>
as you can see, ">" has been escaped.
The same thing happens with xmlQuery (new outdated 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 \<tags>\</tags> </c>
</a>') as xml_data from dual) t
;
Also when using xmlTransform I get the same result. I tried to use
disable-output-escaping="yes"
But it did the opposite - it did not release & lt;
select XMLTransform(
xmlType('<a>
<b>Something to change here</b>
<c>Here is some narrative containing weirdly escaped \<tags>\</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:
- I cannot change the original format - it comes to me this way and I need to save it.
- The original message is so big that changing the message to and from a string (to use regex as a workaround) would not be a trick.
source to share
Root of your problem, apparently, is that the initial value of XML for node C is not valid XML, if it contains >
inside the values instead of >
, not in section a CDATA (also What does the <! [CDATA []]> in the XML? ,
String value:
Here is some narrative containing weirdly escaped <tags>\</tags>
in XML format should really be
<c>Here is some narrative containing weirdly escaped &lt;tags>\&lt;/tags></c>
OR
<c><![CDATA[Here is some narrative containing weirdly escaped <tags>\</tags>]]></c>
I would like to either request a XML patch in the source, or implement some method to sanitize the inputs themselves, such as wrap the <c>
node values in <![CDATA[]]>
. If you need to keep the exact original value and the messages are large, the best I can think of is copying duplicate instances with the original value as a string and storing the "sanitized" value as an XML data type.
source to share
In the end, we managed to do it using java. TO:
- reading xml as clob
- changing it in java
- save it back to the database using java.sql.Connection (for some reason, if we used JdbcTemplate, it complained about casting Long, which was an indication that the string was over 4000 bytes (speaking of pure error, all hail Oracle) and using CLOB Type doesn't really Help. I guess that's a different story though)
When storing data, the oracle does not do any magicians, only updates tend to change the escape characters.
This may not be the answer for everyone, but a good workaround in case you stumble upon the same problem as us.
source to share