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

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.