Unexpected Behaviour of XMLTYPE.TRANSFORM in PL/SQL code

(Doc ID 1141935.1)

Last updated on JULY 13, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

XMLTYPE.TRANSFORM behaves differently in SQL vs PLSQL.

Given the following DDL for a function:

set define off;

create or replace function xml_test return clob as
begin
return xmltype.transform(
-- This is the XML to be transformed
xmltype('<row rownum="3">
   <CC_ID title="ID" type="NUMBER">123456789</CC_ID>
   <MOL title="Structure (mol)" type="STRUCTURE">XXX</MOL>
   <CHIME title="Structure (chime)" type="STRUCTURE">YYY</CHIME>
   <MOLWT title="Mol Weight" type="NUMBER">361.415</MOLWT>
</row>'),
-- This is the stylesheet
xmltype('<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:variable name="new_line" select="'' ''"/>
  <xsl:output method="text"/>

  <xsl:template match="row">
    <xsl:apply-templates select="MOL1" mode="noskip"/>
    <xsl:apply-templates select="MOL" mode="noskip"/>
    <xsl:apply-templates/>
    <xsl:text>$$$$</xsl:text>
    <xsl:value-of select="$new_line"/>
  </xsl:template>

  <xsl:template match="row/MOL | row/MOL1" priority="10" mode="noskip">
    <xsl:value-of select="."/>
  </xsl:template>

  <xsl:template match="row/MOL" priority="10"/>
  <xsl:template match="row/MOL1" priority="10"/>
  <xsl:template match="row/CHIME" priority="10"/>
  <xsl:template match="row/CHIME1" priority="10"/>
  <xsl:template match="row/MOLR" priority="10"/>
  <xsl:template match="row/CHIMER" priority="10"/>
  <xsl:template match="row/*">
    &#62; &#60;<xsl:value-of select="name(.)"/>> (<xsl:value-of select="../@rownum"/>)
    <xsl:value-of select="."/>
    <xsl:value-of select="$new_line"/>
    <xsl:value-of select="$new_line"/>
  </xsl:template>

  <xsl:template match="*"/>

</xsl:stylesheet>')
).getclobval();
end;
/

Note that the output method in the xsl above is text:
   <xsl:output method="text"/>

The function gets created successfully, but calling it fails with errors:

SQL> set define off;
SQL>
SQL> create or replace function xml_test return clob as
  2 begin
  3 return xmltype.transform(
  4 -- This is the XML to be transformed
  5 xmltype('<row rownum="3">
  6 <CC_ID title="ID" type="NUMBER">123456789</CC_ID>
...
...
 45 ).getclobval();
 46 end;
 47 /

Function created.

SQL>
SQL> select xml_test from dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00225: end-element tag "DummyFragment" does not match start-element tag
"MOLWT"
Error at line 10
ORA-06512: at "SYS.XMLTYPE", line 138
ORA-06512: at "XDBTEST.XML_TEST", line 3

no rows selected

However, when the above XML and XSL (saved to files) are passed to XMLTYPE.TRANSFORM from sqlplus, XMLTYPE.TRANSFORM completes without errors:

SQL> select xmltype.transform
  2 (xmltype(getdocument('myxmldoc.xml')),
  3 xmltype(getdocument('mystylesheet.xsl'))).getclobval() as XML_TEST
  4 from dual;

XML_TEST
--------------------------------------------------------------------------------
XXX
    > <CC_ID> (3)
    123456789


    > <MOLWT> (3)
    361.415

$$$$


SQL>

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