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 "&#34" "

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    '<root>
  5       <content>&lt;message&gt;Hi There!&lt;/message&gt;</content>
  6     </root>');
  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: &lt;message&gt;Hi There!&lt;/message&gt;
Converted    : <message>Hi There!</message>
 
PL/SQL PROCEDURE successfully completed.
 
 
SQL> DECLARE
  2  
  3    xml xmltype := xmltype(
  4    '<root>
  5       <content>&lt;message&gt;&#34;&lt;/message&gt;</content>
  6     </root>');
  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: &lt;message&gt;&quot;&lt;/message&gt;
Converted:     <message>"</message>
 
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:

HTH

M.

Marco Gralike Written by:

7 Comments

  1. Gary
    November 17

    The answer is a forum that allows file attachments (at least text). http://www.quest-pipelines.com allows that.
    But if you think its bad for Oracle developers, imagine how it must be for HTML Q&A forums.

  2. November 17

    “Quest-pipelines” as been eaten by the “Toad”(world). Burp. 😉

    Said that; Until now I haven’t found a decent code WP plugin that didn’t eat every XML Schema I fed it. At least that doesn’t happen on the OTN forum so that is not that bad.

Comments are closed.