Oracle 10.1 and 11.2 produce different XML using the same statement

Posted by MindFyer on Stack Overflow See other posts from Stack Overflow or by MindFyer
Published on 2010-03-04T02:08:27Z Indexed on 2010/04/07 22:33 UTC
Read the original article Hit count: 230

Filed under:
|

I am migrating a database from Oracle 10.1 to 11.2 and I have the following problem.

The statement

SELECT
      '<?xml version="1.0" encoding="utf-8" ?>' || (Xml).getClobVal() AS XmlClob
FROM
(
  SELECT
    XmlElement( "Element1",
      (
        SELECT
          XmlAgg(tpx.Xml)
        FROM 
          (
        SELECT
            XmlElement("Element3",XmlForest('content' as Element4)) AS Xml
        FROM dual 
        ) tpx
      ) AS "Element2"
    ) AS Xml
  FROM
    dual
)

On the original 10.1 database produces XML like this...

<?xml version="1.0" encoding="utf-8"?>
<Element1>
  <Element2>
    <Element3>
      <ELEMENT4>content</ELEMENT4>
    </Element3>
  </Element2>
</Element1>

On the new 11.2 system it looks like this...

<?xml version="1.0" encoding="utf-8"?>
<Element1>
  <Element3>
    <ELEMENT4>content</ELEMENT4>
  </Element3>
</Element1>

Is there some environmental variable I am missing that tells Oracle how to format its XML. There are hundreds of thousands of lines of PL/SQL in the database; it would be a mammoth task to rewrite if it turned out they had changed they way Oracle formats XML between versions.

Hopefully someone has come accross this before. Thanks

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about Xml