Oracle query using Xpath to get comma separated List for same node

Question

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>

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

Then , my output was ABCPQR.

No Space or comma was there. Could anyone please help me out for this .

Expected output is :- ABC,PQR

Thanks in advance :)

Luke Woodward Luke Woodward · Accepted Answer · 2016-10-13T20:31:08

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.

Here's an example:

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

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 query using Xpath to get comma separated List for same node

1 Answers