CODEWITHSUNDEEP
oracle11g
Nani
Nani

Reputation: 35

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

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

Upvotes: 3

Views: 2367

Answers (1)

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

  • 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
)

Upvotes: 1

Related Questions