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屋！