Node iteration Pentaho xpath

Question

I need help,

I have an XML file which has data in format:

<GetReservationRS>
  <Reservation>
    <BookingDetails>
      <RecordLocator>WYCZOE</RecordLocator>
      <CreationTimestamp>2016-07-28T11:47:00</CreationTimestamp>
    </BookingDetails>
    <PassengerReservation>
      <Passengers>
        <Passenger id="5" nameType="S" nameId="01.01">
          <LastName>BARRERA</LastName>
          <FirstName>EDITH</FirstName>
          <Seats />
        </Passenger>
        <Passenger id="6" nameType="S" nameId="02.01">
          <LastName>TORRES</LastName>
          <FirstName>ADRIANA</FirstName>
          <Seats />
        </Passenger>
      </Passengers>
    </PassengerReservation>  
  </Reservation>    
</GetReservationRS>

Now I want output to be a text file with column names and records like these (with RecordLocator duplicated by the two Passenger cases):

RecordLocator    LastName   FirstName
WYCZOE           BARRERA    EDITH
WYCZOE           TORRES     ADRIANA

Output data should look like the above table. How can we achieve the looping in pentaho.

Thanks

Answer 1

Below is the java code of it using XPath and VTD-XML...It is coded to be very general purpose.. and it is tested...

import com.ximpleware.*;

public class extractData {
    public static void main(String[] s) throws VTDException {
        VTDGen vg= new VTDGen();
        if (!vg.parseFile("d:\\xml\\table.xml", false)){
            System.out.println("xml not parsed correctly");
            return;
        }
        VTDNav vn = vg.getNav();
        AutoPilot ap = new AutoPilot(vn),
                ap1=new AutoPilot(vn),
                ap2=new AutoPilot(vn),
                ap3=new AutoPilot(vn);
        ap.selectXPath("/GetReservationRS/Reservation");
        ap1.selectXPath("BookingDetails/RecordLocator");
        ap2.selectXPath("PassengerReservation/Passengers/Passenger");
        System.out.println("RecordLocator \t LastName \t FirstName");
        int i=0,j=0,k=-1,l=-1;
        while((i=ap.evalXPath())!=-1){
            String rl = ap1.evalXPathToString();
            while((j=ap2.evalXPath())!=-1){
                if (vn.toElement(VTDNav.FIRST_CHILD,"LastName")){
                    k=vn.getText();
                    vn.toElement(VTDNav.P);
                }
                if (vn.toElement(VTDNav.FIRST_CHILD,"FirstName")){
                    l=vn.getText();
                    vn.toElement(VTDNav.P);
                }

            String s1="";
            String s2="";
            if (k!=-1){
                s1 = vn.toString(k);
            }
            if (l!=-1){
                s2 = vn.toString(l);
            }
            System.out.println(rl+" \t\t "+s1+" \t "+s2);
            }
            ap2.resetXPath();
        }
    }
}