Oracle 10.1 and 11.2 produce different XML using the same statement

Question

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

Answer 1

I don't have access to search Metalink but you may want to see if there are any postings about this functionality difference if you have an account.

I am able to reproduce this issue on 10.2.0.1 vs 11g using your sample query. I did not see any information in the release notes for 11g describing any kind of default behavior change like this. You could also open a case with Oracle Support if you have a support contract.

Answer 2

Looks to me like a possible bug in the v10 since you're not explicitly telling the DBMS that Element2 is an XMLElement. XMLAgg returns a Nodeset and just because you've given it an alias of "Element2" doesn't seem to indicate it should be nested within an element of that name.