sql - Oracle SQL XMLAGG”问题

Question

我正在尝试将 A 聚合到字典列表中。甲骨文的LISTAGG()有效，但确实遇到了 4k 最大字符限制。我尝试过 XMLAGG但现在我得到了“和”代替 ””。请提出解决此问题的最佳方法。 XMLCAST ？最终输出会是listagg()和解决方法是相同的？

<表类=“s-表”><标题>一个N <正文>{"1":"09","2":"11","3":"2010","4":"XYZ","5":""}1{"1":"09","2":"11","3":"2010","4":"XYZ","6":""}2{"1":"09","2":"11","3":"2010","4":"XYZ","7":""}3

select  
  --        '[' ||
  --        LISTAGG(cte.A, ' , ') WITHIN GROUP(
  --                ORDER BY
  --                    cte.N
  --            )
  --            || ']'
  '[' ||
  rtrim(xmlagg(xmlelement(e,cte.A,', ').extract('//text()') order by cte.N).getclobval(),', ')
  || ']' aggr_lsts
from cte;

输出错误:

[{"1":"09","2":"11","3":"2010","4":"XYZ","5":""}, {"1":"09","2":"11","3":"2010","4":"XYZ","6":""}, {"1":"09","2":"11","3":"2010","4":"XYZ","7":""}]

良好的输出:

[{"1":"09","2":"11","3":"2010","4":"XYZ","5":""} , {"1":"09","2":"11","3":"2010","4":"XYZ","6":""} , {"1":"09","2":"11","3":"2010","4":"XYZ","7":""}]

谢谢。

Answer 1

您可以使用 xmlcast 函数正确地将 XML 数据反序列化为 clob。

请注意，XMLType 方法是 deprecated since Oracle 11.2 ，所以使用XMLQUERY和XMLCAST相反。

with sample(col, rn) as (
  select column_value, rownum
  from sys.odcivarchar2list(
    '&',
    '>',
    '<',
    '"',
    'correctly serializable text'
  )
)
select
  rtrim(xmlcast(xmlquery(
    '//text()'
    passing xmlagg(
      xmlelement(e,col,', ')
      order by rn
    )
  returning content
  ) as clob), ', ') as res
from sample

<表类=“s-表”><标题>RES <正文>&、>、<、"、正确可序列化的文本

但是对于 12.2 及更高版本，您可以直接使用 JSON 函数来生成正确的 JSON 数组: JSON_ARRAYAGG (如果我没记错的话，每个对象限制 32k JSON 键)。

with sample(col, rn) as (
  select column_value, rownum
  from sys.odcivarchar2list(
    '{"a": 1, "b": "2"}',
    '{"a": 2, "b": "qwe"}',
    '{"a": 3, "c": "test"}'
  )
)
select
  json_arrayagg(
    col order by rn
  ) as res
from sample

<表类=“s-表”><标题>RES <正文>["{"a": 1, "b": "2"}","{"a": 2, "b": "qwe"}", "{"a": 3, "c": "测试"}"]

fiddle