Oracle使用Xpath查询以获取逗号分隔的同一节点的列表 [英] Oracle query using Xpath to get comma separated List for same node
问题描述
大家好, 如果XML节点中存在相同的节点,我需要一个帮助来获取逗号分隔的列表. 我的XML就是这样,存储在我的数据库中:-
Hello Everyone, I need a help to fetch comma separated list for same node if exists in XML nodes. My XML is like this which is stored in my database:-
<Event>
<Detail>
<List>
<Element>
<name>ABC</name>
<salary>150</salary>
</Element>
<Element>
<name>PQR</name>
<salary>250</salary>
</Element>
</List>
</Detail>
</Event>
我需要使用xpath oracle查询从中获取逗号分隔的名称列表(ABC,PQR). 我已经尝试了很多,当我得到这样的方法时:-
I need to get comma separated name list (ABC,PQR ) from this using xpath oracle query. I have tried alot and when i get a way like this :-
NVL(VALUE (line).EXTRACT ('/Event/Detail/List/Element/name/text()') .getstringval (),'') Name List
然后,我的输出是ABCPQR.
Then , my output was ABCPQR.
没有空格或逗号.谁能帮我这个忙.
No Space or comma was there. Could anyone please help me out for this .
预期输出为:-ABC,PQR
先谢谢您了:)
推荐答案
您是从XML文档中提取值列表,而不是单个值,因此我认为最好将它们全部都选择到其中使用XMLTABLE
的表,然后将它们与LISTAGG
联接在一起.
You're pulling a list of values out of an XML document, rather than a single value, so I think you'd be better off selecting them all into a table using XMLTABLE
and then joining them together with LISTAGG
.
这是一个例子:
SQL> CREATE TABLE xmltest (id INTEGER, x XMLTYPE);
Table created.
SQL> INSERT INTO xmltest (id, x) VALUES (1, XMLTYPE('<Event>
2 <Detail>
3 <List>
4 <Element>
5 <name>ABC</name>
6 <salary>150</salary>
7 </Element>
8 <Element>
9 <name>PQR</name>
10 <salary>250</salary>
11 </Element>
12 </List>
13 </Detail>
14 </Event>'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT t.id, LISTAGG(y.name, ',') WITHIN GROUP (ORDER BY ROWNUM)
2 FROM xmltest t,
3 XMLTABLE('//Element' PASSING t.x COLUMNS name VARCHAR2(1000) PATH 'name') y
4 GROUP BY t.id;
ID
----------
LISTAGG(Y.NAME,',')WITHINGROUP(ORDERBYROWNUM)
--------------------------------------------------------------------------------
1
ABC,PQR
XPath 2.0中有一个函数string-join
,该函数会将字符串值和逗号连接在一起.看来这正是您所需要的,但是可惜Oracle似乎不支持它.
There is a function in XPath 2.0, string-join
, which will join the string values together with commas. It would seem to be just what you need, but alas it seems Oracle doesn't support it.
这篇关于Oracle使用Xpath查询以获取逗号分隔的同一节点的列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!