If you just upgraded your Oracle version and you see inconsistency in behavior of your old SQL\/XML (SQLX) queries, you might want to check if the actual XML structure is still the same.<\/p>\n
Specifically, there is a problem with XMLAgg alias. The fact is that if you run the same code on Oracle 9 or 10 (up to 10.2.0.3) \u00a0\u00a0and later versions, the same query returns different results.\u00a0 \u00a0If you used aliasing to name an aggregated collection you will notice that the alias disappeared in later versions.<\/p>\n
For example, if you run the following code on Oracle 9<\/p>\n
the result will be :<\/p>\n Clearly, similar queries do not work the same way across oracle versions.\u00a0\u00a0 The problem is that it is dropping the alias silently. The query is not failing it just produce a different xml.<\/p>\n Unfortunately, to overcome this, a change in the code is required. You can easily add an additional XMLELEMENT.<\/p>\n This will produce the pre upgrade results:<\/p>\n If you just upgraded your Oracle version and you see inconsistency in behavior of your old SQL\/XML (SQLX) queries, you might want to check if the actual XML structure is still the same. Specifically, there is a problem with XMLAgg alias. The fact is that if you run the same code on Oracle 9 or […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[71,107,68,69,67,70],"class_list":["post-162","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-alias","tag-oracle","tag-sql","tag-sqlx","tag-xml","tag-xmlagg"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=162"}],"version-history":[{"count":30,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/162\/revisions"}],"predecessor-version":[{"id":194,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/162\/revisions\/194"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nwith t as (SELECT 'Emp 1' name FROM dual
\n UNION ALL
\n SELECT 'Emp 2' name FROM dual
\n UNION ALL
\n SELECT 'Emp 3' name FROM dual)
\nSELECT xmlelement(\"EMPLOYEES_DATA\",
\n (SELECT xmlagg(
\n XMLELEMENT(\"EMPLOYEE_NAME\", t.name)
\n )
\n FROM t) as \"EMPLOYEES\").extract('\/')
\n from dual
\n<\/code><\/p>\n
\n<EMPLOYEES_DATA>
\n<EMPLOYEES>
\n <EMPLOYEE_NAME>Emp 1<\/EMPLOYEE_NAME>
\n<EMPLOYEE_NAME>Emp 2<\/EMPLOYEE_NAME>
\n <EMPLOYEE_NAME>Emp 3<\/EMPLOYEE_NAME>
\n<\/EMPLOYEES>
\n<\/EMPLOYEES_DATA>
\n<\/code>
\nHowever, if you run the same query on oracle 11, the result will be<\/p>\n
\n<EMPLOYEES_DATA>
\n<EMPLOYEE_NAME>Emp 1<\/EMPLOYEE_NAME>
\n<EMPLOYEE_NAME>Emp 2<\/EMPLOYEE_NAME>
\n<EMPLOYEE_NAME>Emp 3<\/EMPLOYEE_NAME>
\n<\/EMPLOYEES_DATA>
\n<\/code>
\nNotice that the \u00a0 <EMPLOYEES> tag is missing<\/strong>. Oracle simply ignores the alias.<\/p>\n
\nwith t as\u00a0\u00a0\u00a0 (SELECT 'Emp 1' name FROM dual
\n UNION ALL
\n SELECT 'Emp 2' name FROM dual
\n UNION ALL
\n SELECT 'Emp 3' name FROM dual)
\nSELECT xmlelement(\"EMPLOYEES_DATA\",
\nxmlelement (\"EMPLOYEES\",<\/strong>
\n(SELECT xmlagg(
\nXMLELEMENT(\"EMPLOYEE_NAME\", t.name)
\n)
\nFROM\u00a0\u00a0 t))<\/strong> as \"WILL_BE_IGNORED_ORACLE_11\"<\/strong>).extract('\/')
\nfrom dual;
\n<\/code><\/p>\n
\n<EMPLOYEES_DATA>
\n<EMPLOYEES>
\n<EMPLOYEE_NAME>Emp 1<\/EMPLOYEE_NAME>
\n<EMPLOYEE_NAME>Emp 2<\/EMPLOYEE_NAME>
\n<EMPLOYEE_NAME>Emp 3<\/EMPLOYEE_NAME>
\n<\/EMPLOYEES>
\n<\/EMPLOYEES_DATA>
\n<\/code>
\nAnother way to use SYS_XMLAGG together with XMLFormat \u00a0but this will require code change anyway \u00a0so I believe that the best alternative will be to add an additional xmlelement.<\/p>\n","protected":false},"excerpt":{"rendered":"