dbms_xmlgen.getXMLType works with VARCHAR2 and CLOB with an '&' but not with LONG

(Doc ID 1091822.1)

Last updated on AUGUST 31, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Symptoms

Given the string "I like blue & green", dbms_xmlgen.getXMLType fails with errors when the string with '&' is stored in a LONG datatype.

SQL> -- Create the table
SQL>
SQL> create table temp_kws_strings
  2 (short_string    varchar2(2000),
  3  long_string     long,
  4  clob_string     clob);

Table created.

SQL>
SQL> -- Desc the table
SQL> --
SQL> desc temp_kws_strings
  Name          Null?    Type
  ------------- -------- ----------------------------
  SHORT_STRING           VARCHAR2(2000)
  LONG_STRING            LONG
  CLOB_STRING            CLOB

SQL>
SQL> set define ?
SQL>
SQL> -- Insert a row into the table
SQL> --
SQL> insert into temp_kws_strings( short_string, long_string, clob_string )
  2 values
  3 ('I like blue & green', 'I like blue & green', 'I like blue & green');

1 row created.

SQL>
SQL> -- Display row value
SQL> --
SQL> select * from temp_kws_strings;

SHORT_STRING              LONG_STRING               CLOB_STRING
------------------------- ------------------------- -------------------------
I like blue & green       I like blue & green       I like blue & green

SQL>
SQL> -- Run dbms_xmlGen.getxmltype with VARCHAR2
SQL> --
SQL> select dbms_xmlGen.getXmlType(
  2    'select short_string ' ||
  3    ' from temp_kws_strings')
  4    .extract( '/ROWSET/ROW' ) as TEST
  5  from dual;

TEST
----------------------------------------------------------------------
<ROW><SHORT_STRING>I like blue &amp; green</SHORT_STRING></ROW>

SQL>
SQL> -- Run dbms_xmlGen.getxmltype with CLOB
SQL> --
SQL> select dbms_xmlGen.getXmlType(
  2    'select clob_string ' ||
  3    ' from temp_kws_strings')
  4    .extract( '/ROWSET/ROW' ) as TEST
  5  from dual;

TEST
----------------------------------------------------------------------
<ROW><CLOB_STRING>I like blue &amp; green</CLOB_STRING></ROW>

SQL>
SQL> -- Run dbms_xmlGen.getxmltype with LONG
SQL> --
SQL> select dbms_xmlGen.getXmlType(
  2    'select long_string ' ||
  3    ' from temp_kws_strings')
  4    .extract( '/ROWSET/ROW' ) as TEST
  5  from dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &amp;)
Error at line 3


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