Convert LISTAGG to XMLAGG

Question

I have a query using LISTAGG function that need to convert to XMLAGG in order to handle string concatenation more than 4000 characters:

 LISTAGG(NAME || INPUT || DESC || ' ' || '<cm>' || DATA || '</cm>', CHR(13)||CHR(10)) WITHIN GROUP(ORDER BY NAME) AS ENTRIES

I try the following:

REPLACE(
 REPLACE(
    XMLAGG(
       XMLELEMENT("A",NAME|| INPUT || DESC || ' ' || '<cm>' || DATA || '</cm>', CHR(13)||CHR(10) ORDER BY NAME).getClobaVal(),<A>',''),
   '</A>','[delimiter]') AS ENTRIES

The syntax error encountered at ORDER BY

Answer 1

You missed ) Try please:

XMLAGG(
         XMLELEMENT ("X", FIELD_NAME|| INPUT || DESCRIPTION_KEY || ' ' || '<cm>' || MATCH_DATA || '</cm>', CHR(13)||CHR(10)) ORDER BY FIELD_NAME)

Answer 2

You missed ")" and " ' "

Try this:

REPLACE(
 REPLACE(
    XMLAGG(
       XMLELEMENT("A",NAME|| INPUT || DESC || ' ' || '<cm>' || DATA || '</cm>', CHR(13)||CHR(10) ORDER BY NAME).getClobaVal()),'<A>',''),
   '</A>','[delimiter]') AS ENTRIES