Oracle xmlagg获得正确的输出
Oracle xmlagg获得正确的输出
提问于 2021-03-15 10:11:28
我试图对表中的某些列执行动态枢轴操作。通常，它可以顺利地使用listagg，但是在本例中，由于结果变量大于3K字符，所以我不得不使用xmlagg。但是，我无法获得输出，因为
'COLUMN' as "COLUMN" 这样，我就可以对列进行枢轴处理，这是我习惯于使用listagg的方法。
SELECT
rtrim(XMLAGG(xmlelement(e, ''''
|| agr_name
|| ''' as "'
|| agr_name
|| '"', ', ').extract('//text()')
ORDER BY
agr_name
).getclobval(), ', ') agr_name
FROM
(
SELECT DISTINCT
agr_name
FROM
dat_skills
);相反，我得到的是
'COLUMN; as "COLUMN;,回答 3
Stack Overflow用户
回答已采纳
发布于 2021-03-15 13:27:20
使用xmlcast -它将解决您的几个问题。我没有你的桌子，但下面是另一张桌子的工作原理：
SELECT
rtrim(xmlcast(XMLAGG(xmlelement(e, ''''
|| object_type
|| ''' as "'
|| object_type
|| '"', ', ')
ORDER BY
object_type) as clob), ', ')
object_type
FROM
(
SELECT DISTINCT
object_type
FROM
user_objects
);
OBJECT_TYPE
--------------------------------------------------------------------------------
'FUNCTION' as "FUNCTION", 'INDEX' as "INDEX", 'LIBRARY' as "LIBRARY", 'LOB' as "
LOB", 'PROCEDURE' as "PROCEDURE", 'SEQUENCE' as "SEQUENCE", 'TABLE' as "TABLE",
'TYPE' as "TYPE", 'TYPE BODY' as "TYPE BODY", 'VIEW' as "VIEW"您不再需要提取文本(强制转换为CLOB已经这样做了)，您不需要担心转义和未转义，结果就是一个CLOB。
Stack Overflow用户
发布于 2021-03-15 10:17:28
包括调用UTL_I18N.unescape_reference
SELECT UTL_I18N.unescape_reference (
XMLAGG (XMLELEMENT (
e,
'''' || agr_name || ''' as "' || agr_name || '"',
', ').EXTRACT ('//text()')
ORDER BY agr_name).getclobval ()) agr_name
FROM (SELECT DISTINCT agr_name
FROM dat_skills);与
SQL> SELECT UTL_I18N.unescape_reference (
2 XMLAGG (XMLELEMENT (
3 e,
4 '''' || agr_name || ''' as "' || agr_name || '"',
5 ', ').EXTRACT ('//text()')
6 ORDER BY agr_name).getclobval ()) agr_name
7 FROM (SELECT DISTINCT agr_name
8 FROM dat_skills);
AGR_NAME
--------------------------------------------------------------------------------
'ACCOUNTING' as "ACCOUNTING", 'OPERATIONS' as "OPERATIONS", 'RESEARCH' as "RESEA
RCH", 'SALES' as "SALES",无
SQL> SELECT
2 XMLAGG (XMLELEMENT (
3 e,
4 '''' || agr_name || ''' as "' || agr_name || '"',
5 ', ').EXTRACT ('//text()')
6 ORDER BY agr_name).getclobval () agr_name
7 FROM (SELECT DISTINCT dname agr_name
8 FROM dept);
AGR_NAME
--------------------------------------------------------------------------------
'ACCOUNTING' as "ACCOUNTING", 'OPERATIONS' as &quo
SQL>Stack Overflow用户
发布于 2021-03-15 10:33:10
假设您没有任何其他需要转义的字符，您可以用实际的引号替换"和'：
SELECT REPLACE(
REPLACE(
RTRIM(
XMLAGG(
xmlelement(
e,
'''' || agr_name || ''' as "' || agr_name || '"',
', '
).extract('//text()')
ORDER BY agr_name
).getclobval(),
', '
),
'"',
'"'
),
''',
''''
) AS agr_name
FROM (
SELECT DISTINCT
agr_name
FROM dat_skills
);db<>fiddle https://dbfiddle.uk/?rdbms=oracle_18&fiddle=45e9be34617d73a2da8443a7f09cd1f1
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66635973
复制相关文章
相似问题
腾讯云开发者
