3,491
社区成员
发帖
与我相关
我的任务
分享
SELECT LISTAGG(DATA) WITHIN GROUP(ORDER BY ROWNUM) AS DATA
FROM (SELECT TRIM(EXTRACTVALUE(VALUE(B), '//data/text()')) AS DATA
FROM TMP A, TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.S), '//data'))) B)
WHERE DATA IS NOT NULL
SELECT LISTAGG(DATA) WITHIN GROUP(ORDER BY ROWNUM) AS DATA
FROM (SELECT TRIM(EXTRACTVALUE(VALUE(B), '//data/text()')) AS DATA
FROM TMP A, TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.S), '//data'))) B)
WHERE DATA IS NOT NULL
WITH TMP AS
(SELECT '<?xml version="1.0" encoding="utf-8" ?>
<fetchdatas>
<datas>
<data name="AREANAME" type="String"/>
<data name="XZQMC" type="String">测试市</data>
<data name="JBR" type="String">系统管理员</data>
<data name="FJ" type="String"/>
<data name="XZDW" type="String">测试区</data>
<data name="CXMD" type="String"/>
<data name="QLRS" type="String">小明</data>
<data name="ZJHS" type="String">112345455</data>
<data name="DJJG" type="String">测试不动产</data>
<data name="DJJGLXDH" type="String">4508213</data>
<data name="CZSJ" type="String">2017年10月12日 08:57:36</data>
<data name="CXSJ" type="String">2017年10月12日</data>
<data name="CXBH" type="String">无房2017区号07001869</data>
</datas>
<detail ID="bdccqxxList"/>
<detail ID="qlrInfoList">
<row ID="000001">
<data name="xh" type="String">1</data>
<data name="qlrmc" type="String">小明</data>
<data name="qlrzjh" type="String">112345455</data>
</row>
</detail>
</fetchdatas>' S
FROM DUAL)
SELECT TRIM(EXTRACTVALUE(VALUE(B), '//data/text()')) AS DATA
FROM TMP A, TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.S), '//data'))) B