Parsing parent child XML structure using PL/SQL

Doing some investigation about processing a XML stucture where we have a parent child type relationship where we want to get all the details for each child record and loop through them.
As part of looping through the child records we want the parent details to be available without the need to go back and read the XML again.

The following example displays each RaceLap tag and includes parent information. Including the case where there may be no child RaceLap entries.
The trick for me to understand was using the laptimes column extracted from the v_xml_example as an XMLTYPE as input into the second part of the FROM clause (laps) using the PASSING race.laptimes.


If there are any questions add a comment.


DECLARE
v_xml_example                  SYS.xmltype := xmltype(
'<Races>
<RaceResult>
<Id>743845</Id>
<PlateNo>420</PlateNo>
<Completed_ind>N</Completed_ind>
<Comments>DNF. No laps recorded</Comments>
</RaceResult>
<RaceResult>
<Id>123145</Id>
<PlateNo>233</PlateNo>
<Completed_ind>Y</Completed_ind>
<Comments>Finished after 3 laps</Comments>
<RaceLap>
<Lap>1</Lap>
<Time>34.34</Time>
</RaceLap>
<RaceLap>
<Lap>2</Lap>
<Time>35.66</Time>
</RaceLap>
<RaceLap>
<Lap>3</Lap>
<Time>34.00</Time>
</RaceLap>
</RaceResult>
</Races>');
CURSOR c_race_laps IS 
SELECT race.id,
race.plate_num,
race.completed_ind,
race.comments,
laps.lap,
laps.lap_time
FROM    XMLTABLE('/Races/RaceResult'  -- XQuery string to get RaceResult tag
                PASSING v_xml_example
                COLUMNS
id VARCHAR2(100) PATH 'Id',
plate_num NUMBER(10) PATH 'PlateNo',
completed_ind VARCHAR2(1) PATH 'Completed_ind',
comments VARCHAR2(100) PATH 'Comments',
laptimes XMLTYPE PATH 'RaceLap') race 
LEFT OUTER JOIN -- want parent with child nodes
XMLTABLE('/RaceLap'  -- XQuery string to get RaceLap tag
                PASSING race.laptimes   -- the laptimes XMLTYPE output from the first xmltable containing the laptimes
                COLUMNS
                        lap NUMBER PATH 'Lap',
lap_time NUMBER PATH 'Time') laps 
ON (1 = 1); -- left outer join always join

BEGIN
FOR v_race_laps_rec IN c_race_laps LOOP
dbms_output.put_line('atr id:' || v_race_laps_rec.id ||
' plate_num:' || v_race_laps_rec.plate_num ||
' completed_ind:' || v_race_laps_rec.completed_ind ||
' Comments:' || v_race_laps_rec.comments ||
' Lap Number:' || v_race_laps_rec.lap ||
' Lap Time:' || v_race_laps_rec.lap_time);
END LOOP;
END;