Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (xmldata) AS

SELECT '<Main>

<child>

</child>

<child>

</child>

<child>

</child>

</Main>' FROM DUAL;

1 rows affected

SELECT d.ids,

c.ids

FROM tablename t

CROSS JOIN LATERAL (

SELECT LISTAGG(id, ',') WITHIN GROUP (ORDER BY id) AS ids

FROM XMLTABLE(

'/Main/details'

PASSING XMLTYPE(t.xmldata)

COLUMNS

id NUMBER(5,0) PATH './id'

)

) d

CROSS JOIN LATERAL (

SELECT LISTAGG(id, ',') WITHIN GROUP (ORDER BY id) AS ids

FROM XMLTABLE(

'/Main/child'

PASSING XMLTYPE(t.xmldata)

COLUMNS

child_no FOR ORDINALITY,

xml XMLTYPE PATH '.'

) x

CROSS JOIN XMLTABLE(

'/child/details'

PASSING x.xml

COLUMNS

id NUMBER(5,0) PATH './id'

)

GROUP BY x.child_no

) c