HOWTO: Avoid Conversion of Escape Codes

Sometimes the OTN Forum site scrambles to much code. The answer given / demonstrated on this site here, was asked on the OTN Forum:

"Problem using extract function (PL/SQL) with """ "

but there is no way to show the proper result on the Forum (even here I had to enable some tricks to show it…)

SQL> show define
define "&" (hex 26)

SQL> set define @

SQL> set serveroutput on

SQL> declare
  2  
  3    xml xmltype := xmltype(
  4    '
  5       <message>Hi There!</message>
  6     ');
  7  
  8    partXML xmltype;
  9    partClob clob;
 10  
 11  begin
 12  
 13    partClob := xml.extract('//root/content/text()').getClobVal(); 
 14    dbms_output.put_line( 'Not Converted: '||partClob );
 15    partXML := xmltype(dbms_xmlgen.convert(xml.extract('//root/content/text()').getClobVal(), dbms_xmlgen.ENTITY_DECODE)); 
 16    dbms_output.put_line( 'Converted    : '||partXML.getClobVal() );
 17  
 18  end;
 19  /

Not Converted: <message>Hi There!</message>
Converted    : Hi There!

PL/SQL procedure successfully completed.


SQL> declare
  2  
  3    xml xmltype := xmltype(
  4    '
  5       <message>"</message>
  6     ');
  7  
  8    partXML xmltype;
  9    partClob clob;
 10  
 11  begin
 12  
 13    partClob := xml.extract('//root/content/text()').getClobVal(); 
 14    dbms_output.put_line( 'Not Converted: '||partClob );
 15    partXML := xmltype(dbms_xmlgen.convert(xml.extract('//root/content/text()').getClobVal(), dbms_xmlgen.ENTITY_DECODE)); 
 16    dbms_output.put_line( 'Converted:     '||partXML.getClobVal() );
 17  
 18  end;
 19  /

Not Converted: <message>"</message>
Converted:     "

PL/SQL procedure successfully completed.

SQL> set define &
SQL>

The answer given is based on an example of former colleague, Peter Boekelaar, while dealing with the same issue…

See also the manuals about:

• DBMS_XMLGEN.CONVERT (ENTITY_ENCODE | ENTITY_DECODE )

HTH

M.