TRANSLATE not working inside XMLAGG

201 Views Asked by At

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".
0

There are 0 best solutions below