Skip to Main Content

REPLACE not working - ASCII character - XML element

Jeevan AnandJun 7 2022

Hi,
I would like to persist the special character as it as in table column - CLOB.

When i execute below query, only & is replaced with & value, Other values are not replacing with actual special character.

Can you please assist.
Query:
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
RTRIM(XMLAGG(XMLELEMENT(E,'<>''"&',' \n ','Test&').EXTRACT('//text()')).GETCLOBVAL(),' \n '),
CHR(60)||'lt;',CHR(60)),
CHR(39)||'apos;',CHR(39)),
CHR(38)||'amp;',CHR(38)),
CHR(62)||'gt;',CHR(62)),
CHR(34)||'quot;',CHR(34)) as repl from dual;

Query Output:
<>'"& \n Test&

Expected Output:
<>''"& \n Test&

This post has been answered by odie_63 on Jun 7 2022
Jump to Answer
Comments
Post Details
Added on Jun 7 2022
3 comments
64 views