Last updated on FEBRUARY 08, 2017

Applies to:

Symptoms

When using XMLTRANSFORM in 11g output characters do not escape properly. Following simple testcase demonstrates the problem -

set serveroutput on
set define off
declare
   l_xsl xmltype;
   l_xml xmltype;
   V_STR_ESCAPE clob;
   V_RESULT varchar2(5000);
   v_str  varchar2(1000):='1.2500.RD.NITROSTEEL.CARBON.TG &
P.288.0000';  BEGIN


   l_xsl := XMLTYPE('<?xml version="1.0"?>
                     <xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                     <xsl:output method="text"/>
                     <xsl:template match="/">
                     <xsl:value-of select="//name"/>
                     </xsl:template>
                     </xsl:stylesheet>');

   SELECT xmlelement("name", v_str) INTO l_xml FROM dual;

   SELECT xmltransform(l_xml,l_xsl).getClobVal() INTO v_str_escape
FROM dual;

   SELECT DBMS_LOB.SUBSTR(v_str_escape,
NVL(dbms_lob.getlength(v_str_escape),0),1) INTO  v_result FROM dual;

   dbms_output.put_line( v_result) ;

 EXCEPTION
 when OTHERS then
  dbms_output.put_line('ERROR');
 END;
/


Above code in 10g db handles the escape '&' charecter but not in 11g as shown in below output -

10.2.0.5 Output

1.2500.RD.NITROSTEEL.CARBON.TG &amp; P.288.0000

11.1.0.7 output

1.2500.RD.NITROSTEEL.CARBON.TG & P.288.0000

11.2.0.4 output

1.2500.RD.NITROSTEEL.CARBON.TG & P.288.0000

12c output

1.2500.RD.NITROSTEEL.CARBON.TG & P.288.0000

Cause