Search code examples
xmloracle-databasetransformxmltype

Add tag to xml filed in oracle (transform xml)

I have table with filed "XmlInfo". This field is a varchar2 and contain XML. Don't ask why it is varchar2 - this is old database structure.

I need to "add" one tag in this xml. So I used xmltype.createXML and APPENDCHILDXML combination. Problem is with output.

Original field is without indent. So first I use xmlserialize with "no indent" option. Unfortunately this is available only in oracle 11, and I need to this query work on both 10 and 11 oracle.

So I use transform. But again I'm stuck.

Original value:

<info><Managed>False</Managed></info>

Expected:

<info><Managed>False</Managed><ChangedDate>2013-09-11</ChangedDate></info>


I use this query

select xmltype.createXML(t1.XmlInfo).APPENDCHILDXML('//info',XMLTYPE.CREATEXML('<ChangedDate>'|| to_char(sysdate, 'yyyy-mm-dd') ||'</ChangedDate>'))
  .transform(xmltype(
    '<?xml version="1.0"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output omit-xml-declaration="yes" indent="no"/><xsl:template match="@*|node()"><xsl:copy><xsl:apply-templates select="@*|node()"/></xsl:copy></xsl:template></xsl:stylesheet>'
  )).getStringVal()
from table1 t1

Result in Oracle 11 (as expected):

<info><Managed>False</Managed><ChangedDate>2013-09-11</ChangedDate></info>

Result in Oracle 10 (there is new line char):

<info>
<Managed>False</Managed>
<ChangedDate>2013-09-11</ChangedDate>
</info>



What is wrong with this transform, or is there any way to write this query and work as expected in both version of oracle?

Remarks: In next step I'll have to update this value, so I'll use "updatexml". This is why I cannot just concatenate two string.

Solution

  • I solve the problem by removing completely xml engine and play with plain text...
    By combining substr, instr and case statement I create ugly, obscure SQL that inject correct date in specific place.
    Depending on existence of ChangedDate string and old value in this node.

    This is workaround so I marked question as answered. But if some one knows the real answer for indentation problem, let me know. Because next update could be not so easy to handle with just string functions...