GROUP BY XMLAGG() fails with ORA-31011, ORA-19202, LPX-00234 in 11.2 (Doc ID 1366473.1)

Last updated on OCTOBER 16, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

GROUP BY XMLAGG() query that ran without errors in 10.2 is failing in 11.2:

SQL> set echo on long 9999 lines 200 pages 200
SQL> WITH
  2    readings AS
  3    (SELECT '7.0.4.1.0.12.0.0.0.3.73' AS reading_type_code ,
  4      '60-minute DeltaData Forward Energy (kVARh)' AS meas_name ,
  5      'INTERVAL' AS cim_read_type ,
  6      '3.0.0' AS quality_code ,
  7      'kWh' AS meas_uom ,
  8      SYSDATE AS read_time ,
  9      335.22 AS read_value
 10    FROM dual
 11    UNION ALL
 12    SELECT '7.0.4.1.0.12.0.0.0.3.72' AS reading_type_code ,
 13      '60-minute DeltaData Forward Energy (kWh)' AS meas_name ,
 14      'INTERVAL' AS cim_read_type ,
 15      '3.0.0' AS quality_code ,
 16      'kWh' AS meas_uom ,
 17      SYSDATE AS read_time ,
 18      35.22 AS read_value
 19    FROM dual
 20    )
 21  SELECT XMLROOT (
 22      XMLELEMENT ("tnsc:ResponseMessage",
 23        xmlattributes ('http://www.docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' AS "xmlns:tns",
 24                       'http://www.w3.org/2000/09/xmldsig#' AS "xmlns:tnsa",
 25                       'http://www.docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd' AS "xmlns:tnsb",
 26                       'http://www.iec.ch/TC57/2008/schema/message' AS "xmlns:tnsc",
 27                       'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
 28                       'http://www.iec.ch/TC57/2008/schema/message Message1.4.xsd' AS "xsi:schemaLocation" ),
 29  (SELECT XMLELEMENT("tns:IntervalBlocks",
 30    XMLAGG(
 31     XMLCONCAT(
 32      XMLCONCAT(
 33      XMLAGG(
 34       XMLFOREST(
 35         XMLCONCAT(
 36          XMLFOREST (DECODE(cim_read_type,'INTERVAL',c.read_value,NULL) AS "tns:value" ),
 37          XMLFOREST (xmlforest(c.quality_code AS "quality") AS "tns:ReadingQualities")
 38              ) AS "tns:IntervalReadings"))
 39           ,XMLELEMENT ("tns:ReadingType",xmlattributes (c.reading_type_code AS "ref"))
 40           )))
 41           )
 42       FROM readings c
 43       GROUP BY C.READING_TYPE_CODE,CIM_READ_TYPE)
 44                       ), VERSION '1.0" encoding="UTF-8' ) AS oxml from dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00234: namespace prefix "tns" is not declared
Error at line 1


no rows selected

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms