SQL & PL/SQL

Hello All,

I am writing a query to concatenate values on a certain column. I could not use listagg because of the character length constraints (>4000 characters)

Here is an example:

with cte

as (select 34 as student ,1 as seq,'Taylor''s Math' as Subject from dual

    Union

    Select 34 as student ,2 as seq,',Johnny "O"hare' as subject from dual)

select student,RTRIM(XMLAGG( case when TRIM(Subject) is not null then xmlelement(E,TRIM(Subject) || CHR(10) ) end order by TO_NUMBER(SEQ)).extract('//text()').GETCLOBVAL() ,CHR(10)) as LINE

from cte

GROUP BY student

Actual Result:  34,"Taylor's Math,Johnny "O"hare"

Expected Result: 34,,"Taylor's Math,Johnny "O"&hare"

The apostrophe's get replaced by 's and the Double quotes by &quot. How do I handle this scenario. Also, what are the special characters i should be concerned about.

Thanks In advance