Parsing XML with Multiple Outer Structures

[amc77]

I have an XML that has multiple structures in it at the Invoice and Line level and I am trying to find a good way to parse this.  The XML structure is such that a batch can have multiple invoices, and invoice can have multiple lines.  However, certain address elements can be populated at either (or both) the invoice and the line level.  I can't figure out how to parse the child element (such as BILL_TO) and determine what invoice/line it came from.

<BATCH>

   <INVOICE1>

      <BILL_TO>

      <SHIP_TO>

           <LINE1>

               <BILL_TO>

               <SHIP_TO>

         <LINE2>

               <SHIP_TO>

   <INVOICE2>

             <LINE1>

               <BILL_TO>

             <LINE2>

             <BILL_TO>

My end goal is to have every single line parsed to a single row in a table with the batch/invoice header details repeating for each line.  I've gotten about 50% of the way there but I'm stuck on these structural elements that are repeated at the invoice and line level.

Any creative ideas would be appreciated.

[DavidP · Accepted solution]

Have a look at the Parse XML macro. It flattens multi-layered XML files and then you can manipulate the data yourself after that. You can find it on the public Alteryx Gallery, but here's a copy attached.

[BenMoss]

Hi!

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

Learn more about Accepted Solutions here.

Thank you!

[MoinShaikBasha]

This is a great macro where we are getting all the data, however I see the data is coming in 2 columns. Can we have the data in a tabular format where Name and value columns are crosstabbed and the data will be in a Tabular format based on the data structure. I am finding it difficult to stack the relevant data in a column.