How to replace ' or any special character in when using XMLELEMENT Oracle

Question

I have the below query. How to keep the apostrophe (') intact and not getting it replaced by &apos There are other characters also I want to handle like &

SELECT RTRIM(XMLAGG(XMLELEMENT(E,'I''m'||':')).EXTRACT('//text()'),':')
  FROM dual;

Output:

I'm

Thanks.

Answer 1

You can make use of utl_i18n package and unescape_reference() function in particular. Here is an example:

clear screen;
column res format a7;

select utl_i18n.unescape_reference(
          rtrim(
               xmlagg( -- use of xmlagg() function in 
                       -- this situation seems to be unnecessary 
                       XMLELEMENT(E,'I''m'||':')
                      ).extract('//text()'),':'
                )
        ) as res
 from dual;

Result:

RES   
-------
I'm