My XML looks like this:
<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 output:
->1
-->John
-->Jack
->2
-->Scott
-->Chuck
-->Kim
My current code looks like this:
<code>
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 ID
FROM TABLE(XMLSEQUENCE(EXTRACT(X, '//data/row'))) P)
LOOP
DBMS_OUTPUT.PUT_LINE('-->' || R.id);
END LOOP;
END;
</code>
I would need one more loop inside a row to loop through name tag, but I don't know how to do it.
A little help would be appreciated.