Oracle SQL XMLAGG "e; Issue

Question

我正在尝试将A聚合到一个字典列表中。Oracle的LISTAGG()工作，但确实遇到4k最大字符限制。我试过用XMLAGG，但现在我得到了"one_answers"而不是"请建议解决这个问题的最佳方法。XMLCAST吗?listagg()的最终输出和周围的工作是否相同?

<表类> N tbody><<tr>{"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

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

请注意，XMLType方法自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

RES&，>， <， "，正确序列化的文本

不要对JSON数据使用XML函数。从Oracle 12.2开始，使用JSONAGG来聚合行:

SELECT JSON_ARRAYAGG(a FORMAT JSON ORDER BY n RETURNING CLOB) As output
FROM   table_name

注意:您需要FORMAT JSON将您的输入视为有效的JSON，否则它将被视为字符串并加引号。您还需要RETURNING CLOB，否则默认返回类型是VARCHAR2，限制为4000个字符，并且会给您与LISTAGG相同的问题。

对于样本数据:

CREATE TABLE table_name (A, N) AS
SELECT '{"1":"09","2":"11","3":"2010","4":"XYZ","5":""}', 1 FROM DUAL UNION ALL
SELECT '{"1":"09","2":"11","3":"2010","4":"XYZ","6":""}', 2 FROM DUAL UNION ALL
SELECT '{"1":"09","2":"11","3":"2010","4":"XYZ","7":""}', 3 FROM DUAL;

输出: