oracle11gnsxmlelement

How to remove ELEMENTs from XMLELEMENT in Oracle 11g


I am trying to get rid of few changes while using XMLELEMENT. Below is an example:

SELECT XMLAGG(XMLELEMENT(E, ' ---- test using & (Ampersand) and '' (Apostrophe) ---- ')).EXTRACT('//text()').GETCLOBVAL() FROM DUAL;

It produces a HUGECLOB:

-- test using & (Ampersand) and ' (Apostrophe) --

But I need the exact content:

-- test using & (Ampersand) and ' (Apostrophe) --

In XMLELEMENT its mandatory to pass Element Name (any character or string)

Is there any other option ?

Thanks in advance.


Solution

  • Found related answer:

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

    Use of utl_i18n.unescape_reference

    If you have anymore better option please reply. Thanks