My Oracle Support Banner

Unexpected Behaviour of XMLTYPE.TRANSFORM in PL/SQL code (Doc ID 1141935.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 11.1 to 11.2]
Information in this document applies to any platform.


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
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>
-- This is the stylesheet
xmltype('<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="" 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:value-of select="$new_line"/>

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

  <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 match="*"/>


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> 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> select xml_test from dual;
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00225: end-element tag "DummyFragment" does not match start-element tag
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;

    > <CC_ID> (3)

    > <MOLWT> (3)






To view full details, 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 a vibrant support community of peers and Oracle experts.