How to do multiple loops through XML in PL / SQL

Question

My XML is as follows

<data> <row> <id>1</id> <name>John</name> <name>Jack</name> </row> <row> <id>2</id> <name>Scott</name> <name>Chuck</name> <name>Kim</name> </row> </data> 

I would like to:

 ->1 -->John -->Jack ->2 -->Scott -->Chuck -->Kim 

My current code is as follows:

  DECLARE X XMLTYPE := XMLTYPE('<?xml version="1.0" ?> <data> <row> <id>1</id> <name>John</name> <name>Jack</name> </row> <row> <id>2</id> <name>Scott</name> <name>Chuck</name> <name>Kim</name> </row> </data>'); BEGIN FOR R IN (SELECT EXTRACTVALUE(VALUE(P), '/row/id/text()') AS NAME FROM TABLE(XMLSEQUENCE(EXTRACT(X, '//data/row'))) P) LOOP DBMS_OUTPUT.PUT_LINE('-->' || R.NAME); END LOOP; END; 

I will need another loop inside the line to scroll the name tag, but I don't know how to do it.

It would be helpful to appreciate a little help.

Answer 1

I solved it myself:

  DECLARE X XMLTYPE := XMLTYPE('<?xml version="1.0" ?> <data> <row> <id>1</id> <promet> <name>John</name> <name>Jack</name> </promet> </row> <row> <id>2</id> <promet> <name>Scott</name> <name>Chuck</name> <name>Kim</name> </promet> </row> </data>'); BEGIN FOR R IN (SELECT EXTRACTVALUE(VALUE(P), '/row/id/text()') AS ID, EXTRACT(VALUE(P), '/row/promet') AS PROMET FROM TABLE(XMLSEQUENCE(EXTRACT(X, '//data/row'))) P) LOOP DBMS_OUTPUT.PUT_LINE('-->' || R.ID); FOR R1 IN (SELECT EXTRACTVALUE(VALUE(T1), '/name/text()') AS NAME FROM TABLE(XMLSEQUENCE(EXTRACT(R.PROMET, 'promet/name'))) T1) LOOP DBMS_OUTPUT.PUT_LINE('-->' || R1.NAME); END LOOP; END LOOP; END;