Parsing parent child XML structure using PL/SQL

Source


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;