12

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Rajiv A
  • 941
  • 5
  • 14
  • 30

2 Answers2

19

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
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
  • 2
    This one have a limitation on 4000 characters – Slava Lenskyy Oct 03 '14 at 13:09
  • 2
    @Skoffer Yes it has. To overcome this limitation the `dbms_xmlgen` package and specifically `convert()` function [can be used](http://stackoverflow.com/questions/23683423/is-oracles-extract-function-breaking-the-noentityescaping-in-the-xmlelement/23684068#23684068) – Nick Krasnov Oct 03 '14 at 13:37
  • That helps. Thanks. – IMJS Aug 24 '16 at 17:56
7
SELECT dbms_xmlgen.convert( xmlagg(XMLELEMENT(E,'I''m'
  ||':')).extract('//text()').getclobval() ,1)
FROM dual;

I'm:

Toolkit
  • 10,779
  • 8
  • 59
  • 68