How to remove an extra char which is coming in XMLAGG() output

Question

Im using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

We replaced LISTAGG() with XMLAGG() to avoid concatenation error. when i check the lenght of charecters from both of the fuction output, XMLAGG() giving an extra char in length. Could you please suggest me how can i overcome this issue.

Please find the below sql and out put

XMLAGG():

SELECT TO_CHAR (
      SUBSTR (
         XMLAGG (XMLELEMENT (e, table_name, CHR (13)).EXTRACT (
                    '//text()') ORDER BY tablespace_name).GetClobVal (),
         1,
         2000))
      AS str_concate,
   LENGTH (
      TO_CHAR (
         SUBSTR (
            XMLAGG (XMLELEMENT (e, table_name, CHR (13)).EXTRACT (
                       '//text()') ORDER BY tablespace_name).GetClobVal (),
            1,
            2000)))
      AS str_length
FROM all_tables
WHERE table_name = 'TEST_LOAD

OUTPUT:

STR_CONCATE                  STR_LENGTH

TEST_LOAD  TEST_LOAD              26

LISTAGG()

SELECT LISTAGG (SUBSTR (table_name, 1, 2000), CHR (13))
      WITHIN GROUP (ORDER BY tablespace_name)
      AS str_concate,
   LENGTH (
      LISTAGG (SUBSTR (table_name, 1, 2000), CHR (13))
         WITHIN GROUP (ORDER BY tablespace_name))
      AS str_length
FROM all_tables
WHERE table_name = 'TEST_LOAD';

OUTPUT:

STR_CONCATE                  STR_LENGTH

TEST_LOAD TEST_LOAD               25

Replace CHR(13) with some other character and run the query. You will see that in XMLAGG, that character is appended at the end, while in LISTAGG it is not. — Noel, Nov 19 '14 at 08:30
Same CHR(13) i have in LISTAGG() also, but its not giving any problem — Nani, Nov 19 '14 at 08:35
I suppose @Eat Å Peach proposed to replace chr(13) with another (visible) character to show you that XMLAGG adds this separator after each table_name (even after the last) — Multisync, Nov 19 '14 at 08:56
yes i removed CHR(13) and checked with ',' then i used rtrim(...., ',') to eliminate the last one. Where as samething is not working with chr(13) :( — Nani, Nov 19 '14 at 10:22

Tomáš Záluský · Answer 1 · 2021-11-30T22:03:43.830

In case of XMLELEMENT, you actually create node of XML tree with two children: table_name and CHR(13). (May be it finally looks like single node since both are texts but it is not important.) It is expansion of value_expr nonterminal. The substantial thing is the node is not aware of other nodes and CHR(13) is added to every node as its suffix or, in other words, terminator.
In case of LISTAGG, you describe aggregation of multiple elements. In this case, your CHR(13) serves as delimiter (see syntax diagram) which is put between elements. It is separator rather than terminator.

Since XMLAGG does not suffer with 4000 char limit, I usually prefer XMLAGG. If separator is needed, I recommend to prepend it before each value and cut first occurence using substr. Appending after is possible but makes expression harder.

substr(
  xmlagg(
    xmlelement(e, ', ' || table_name).extract('//text()')
    order by tablespace_name
  ).getclobval(),
  3   -- length(', ')+1
)

How to remove an extra char which is coming in XMLAGG() output

1 Answers1