How to replace ' or any special character in when using XMLELEMENT Oracle
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
SELECT dbms_xmlgen.convert( xmlagg(XMLELEMENT(E,'I''m' ||':')).extract('//text()').getclobval() ,1)FROM dual;
I'm: