Search code examples
oraclestring-concatenationlistaggxmlelement

Convert LISTAGG to XMLAGG

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

Solution

  • You missed ) Try please:

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