Challenge #37: Parsing a Raw XML File

[RoDO]

My solution

Spoiler

[SueDonim]

Don't really understand why I could not see the shipping data (even when I copied the input into Excel).  But when run through Text to Columns, it is there.  Even so, I was able to get pretty good solution

Spoiler

Process:
- Data cleanup to get rid of a couple of unneeded fields
- Add Record IDs for later cross-tab
- First Text to Columns: Split to Rows on "<" - this provides what will become the Field Name (but currently includes the Values as well - separated by ">")
- Second Text to Columns: Split to Columns on ">" - this splits the field name from the values
- Formula adds "Bill to" or "Ship to" to the values field (enabling a cross-tab to be employed to split these values)
- Data Cleansing - to get rid of whitespace that was messing up the filter
- Filter to remove records where values are empty
- Two Crosstabs - on Record ID, and other common data, columns are headers, values are values (the two columns derived above)
  -- First one takes the first value - Billing info
  -- Second one takes the last value - Shipping info
- Join on Record ID
- Select to get rid of un-needed fields, rename, and re-order (I know, I could have used embedded select in the Join, but I am working on limited screen real estate and found this simpler)
- Formula to create Null for phone and email are same for Billing and Shipping (although it seems to be based on just the phone)

[T_Willins]

Reposting to add attachment.  NOTE:  This was completed prior to changes noted in thread start.

Spoiler

[justindavis]

Unfamiliar with XML so this took a good bit longer than I wanted, but learned some new stuff

Spoiler

[dsmdavid]

Previously missing the file.

[ZenonH]

Even though I've done work projects for XML using Alteryx before, I always have some difficulty sorting out structure in my head. This solution is cleaned up LOTS from my original. 

Spoiler

Spoiler

[SimonSturges]

I liked this one

Spoiler

[Jonathan-Sherman]

Challenge 37 is done!