oracle11g - 如何删除 XMLAGG() 输出中的额外字符

Question

我正在使用 Oracle 数据库 11g 企业版 11.2.0.4.0 版 - 64 位生产

我们用 XMLAGG() 替换了 LISTAGG() 以避免连接错误。当我从两个函数输出中检查字符的长度时，XMLAGG() 给出了一个额外的字符长度。您能否建议我如何解决这个问题。

请找出下面的sql并输出

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

输出:

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';

输出:

STR_CONCATE                  STR_LENGTH

TEST_LOAD TEST_LOAD               25

Answer 1

• 在 XMLELEMENT 的情况下，您实际上创建了具有两个子节点的 XML 树节点:table_name 和 CHR(13)。 (可能它最终看起来像单个节点，因为两者都是文本，但这并不重要。)它是 expansion of value_expr nonterminal .重要的是该节点不知道其他节点，CHR(13) 被添加到每个节点作为其后缀，换句话说，终止符。

• 在 LISTAGG 的情况下，您描述了多个元素的聚合。在这种情况下，您的 CHR(13) 用作 delimiter (see syntax diagram)放在元素之间。它是分隔符而不是终止符。

由于 XMLAGG 不受 4000 char limit 的影响，我通常更喜欢 XMLAGG。如果需要分隔符，我建议将其添加到每个值之前，并使用substr 剪切首次出现的位置。追加在后面是可能的，但会使表达更难。

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