Parse Nested XML

[JonBCOKE]

I have XML data downloaded from an API that I am trying convert to a table using XML Parser. The issue is that for each DriverLog there is a LogDetails that can contain 0...X number of LogDetail which each contain additional elements. I am looking for the output to be one table row per LogDetail, with the DriverLog elements repeated on each row. The sample below is a heavily reduced list of data points but illustrates the correct structure.

XML Sample:

<ArrayOfDriverLog>
 <DriverLog>
  <DriverID>123</DriverID>
  <DriverName>Driver123</DriverName>
  <LogDetails>
   <LogDetail>
    <CityName>City1</CityName>
    <RecordSID>Record1</RecordSID>
   </LogDetail>
   <LogDetail>
    <CityName>City2</CityName>
    <RecordSID>Record2</RecordSID>
   </LogDetail>
  </LogDetails>
  <OnDutyTimeToday>25</OnDutyTimeToday>
 </DriverLog>
 <DriverLog>
   <DriverID>234</DriverID>
   <DriverName>Driver2</DriverName>
   <LogDetails>
    <LogDetail>
     <CityName>City3</CityName>
     <RecordSID>Record3</RecordSID>
    </LogDetail>
    <LogDetail>
     <CityName>City4</CityName>
     <RecordSID>Record4</RecordSID>
    </LogDetail>
  </LogDetails>
  <OnDutyTimeToday>52</OnDutyTimeToday>
 </DriverLog>
</ArrayOfDriverLog>

Desired Output:

DriverID	DriverName	CityName	RecordSID	OnDutyTimeToday
123	Driver1	City1	Record1	25
123	Driver1	City2	Record2	25
234	Driver2	City3	Record3	52
234	Driver2	City4	Record4	52

[DavidP]

Here's one way of doing it, although it's not the prettiest!

[JonBCOKE · Accepted solution]

I kept fiddling with this issue and discovered that, with some specific settings, 2 XML Parse-rs will handle this.

XML Parse #1

I simply needed to get the Outer XML data to parse again later.

(I also had to strip the _OuterXML columns in final processing but that was minor)

XML Parse #2

The 2nd XML Parse was where the stroke of genius occurred - I had to set the Field with XML Data as the LogDetails_OuterXML (that contained the sub tree code).

Then I set a Specific Child Name as LogDetail (the individual log childs in the sub tree).

(Return Outer XML was not necessary as I had finished processing)

From there, the Parser automatically did what I needed, repeating the data from the upper level with each Child.

[Mirco_Kunz]

Could you please share you workflow, i got the exact same problem