I want to use TRANSLATE
to replace invalid XML characters in a string that's used in XMLAGG
instead of nested REPLACE
due to the large number of characters that need to be replaced:
select c.key,
cast( substr( xmlserialize( xmlagg( xmltext( concat( ' | ',
replace(replace(replace(replace(c.comment_text,chr(160),' '), chr(191),' '), chr(176),' '), chr(190),' ')
--translate(c.comment_text, ' ', chr(160)||chr(176)||chr(190)||chr(191))
) ) order by c.comment_date desc ) as clob ), 3, 4000 ) as varchar2) as sum_comment
from comments c
where c.comment_date > current_timestamp - 90
group by c.key
If I run this outside of the XMLAGG
I can see that both the TRANSLATE
and REPLACE
are removing the invalid characters, but inside the XMLAGG
I get the error I'm trying to avoid when using TRANSLATE
:
An illegal XML character "#xA0" was found in an SQL/XML expression or function argument that begins with string " | A".