17,088
社区成员
发帖
与我相关
我的任务
分享
WITH TMP AS
(SELECT XMLTYPE('<represent_papers>
<represent_paper seq_no="1">
<paper_name>aaa</paper_name>
</represent_paper>
<represent_paper seq_no="2">
<paper_name>bbb</paper_name>
</represent_paper>
<represent_paper seq_no="3">
<paper_name>ccc</paper_name>
</represent_paper>
</represent_papers>') AS COL
FROM DUAL)
SELECT LISTAGG(EXTRACTVALUE(VALUE(T1), 'paper_name'), ',') WITHIN GROUP(ORDER BY 1)
FROM TMP T, TABLE(XMLSEQUENCE(EXTRACT(COL, '//paper_name'))) T1
WITH TMP AS
(SELECT XMLTYPE('<represent_papers>
<represent_paper seq_no="1">
<paper_name>aaa</paper_name>
</represent_paper>
<represent_paper seq_no="2">
<paper_name>bbb</paper_name>
</represent_paper>
<represent_paper seq_no="3">
<paper_name>ccc</paper_name>
</represent_paper>
</represent_papers>') AS COL
FROM DUAL)
SELECT T.*, NVL(TRIM(EXTRACTVALUE(VALUE(T1), 'paper_name')), 'NULL')
FROM TMP T,
TABLE(CAST(XMLSEQUENCE(EXTRACT(COL, '//paper_name')) AS
XMLSEQUENCETYPE)) T1