Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Parse Nested XML

Highlighted
5 - Atom

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:

DriverIDDriverNameCityNameRecordSIDOnDutyTimeToday
123Driver1City1Record125
123Driver1City2Record225
234Driver2City3Record352
234Driver2City4Record452
14 - Magnetar

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

 

DavidP_0-1585222150195.png

 

Highlighted
5 - Atom

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.

 

Labels