Oracle XML - How to escape text between tags.

Question

If i have a CLOB column with the following xml document

<xml>
<Country>Trinidad & Tobago </Country>
</xml>

I want to escape the ampersand but not the tags.

I know i can use this approach:

Select DBMS_XMLGEN.CONVERT(country) from country_list;

The above query escapes all the special characters. i.e. < > and &. Is there a way i can only escape anything that is between the tags. I.e. the output after escaping is

<xml>
<Country>Trinidad &amp; Tobago </Country>
</xml>

Answer 1

Use REGEXP_REPLACE in a manner similar to the following:

SELECT REGEXP_REPLACE(COUNTRY,
                      '(<.*>.*)(&)(.*</.*>)',
                      '\1&amp;\3') AS REPLACEMENT_TEXT
  FROM COUNTRY_LIST;

SQLFiddle here

Share and enjoy.