Oracle XmlAgg function with case when

Question

I have a query with joined tables where there are two columns that need to be compared and then need to be concatinated

select tc.id$ as id, case
when a.comment is null and b.comment is null then ''
when a.comment is null and b.comment is not null then RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES ('Updated from null -> ' || b.comment || ';' AS "Seg"))ORDER BY b.comment ASC).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),';')
when a.comment is not null and b.comment is null then RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES ('Updated to null from || ' a.comment || ';' AS "Seg"))ORDER BY a.comment ASC).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),';')
when a.comment is not null and b.comment is not null and a.comment <> b.comment then
    RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES ('Updated from '|| a.comment || '->' || b.comment ||';' AS "Seg"))ORDER BY a.comment ASC).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),';')
else ''end as TESTER_COMMENT_UPDATED from tableA tc left join htableA a on a.id$=tc.id$ left join htableA b on b.id$=tc.id$group by tc.id$

Unfortunately I am getting string concatenation is too long

so tried by adding.getClobVal() after EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg')

Unfortunately it is throwing inconsistent datatypes: expected CHAR got CLOB error

so tried by adding case statements inside xml attributes like below:

        RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES (case 
when a.comment is not null and b.comment is not null and a.comment <> b.comment then
      'Updated from ' || a.comment || '->' || b.comment when a.comment is null and b.comment is not null then 'Updated from null -> ' || b.comment
      when a.comment is not null and b.comment is null then 'Updated to null from ' || a.comment
    else '' end as seg

))).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg').getClobVal(),';')

script is executing with out errors but no data output. Is there a way to use xmlaggs with case when or nested?

Answer 1

Finally It worked

when a.comment is null and b.comment is null then null
when a.comment is null and b.comment is not null then  
    XMLELEMENT (E,
      XMLELEMENT(c1, 'Updated from '''' to: '),
      XMLELEMENT(c2, b.comment)
    ).extract('//text()').getClobVal()  
when a.comment is not null and b.comment is null then 
    XMLELEMENT (E,
      XMLELEMENT(c1, 'Updated to '''' from: '),
      XMLELEMENT(c2, a.comment)
    ).extract('//text()').getClobVal() 
when a.comment is not null and b.comment is not null and a.comment <> b.comment then 
    XMLELEMENT (E,
      XMLELEMENT(s, 'Updated from: ' ||CHR(13)),
      XMLELEMENT(c1, a.comment),
      XMLELEMENT(sep, CHR(13) ||' to: ' || CHR(13)),
      XMLELEMENT(c2, b.comment)
    ).extract('//text()').getClobVal()