Alteryx having trouble reading an XML file that is readable by Excel

[lucasyliu]

Alteryx seems having trouble reading an XML file where it opens fine in Excel. See attached.

Can anyone enlighten me?

[BrandonB]

I was able to pull the data in the Excel file by specifying that I wanted the XML Child Element Name "Data". Alteryx is going to allow you to read in ALL of the XML, not just the data fields that you are seeing in Excel, so it will also include things like styling, fonts, etc. 

 

[benakesh]

Hi @lucasyliu ,

How was this xml file created ?  

This works : Open this xml  file in excel and save  as  'xlsx'   and then Alteryx can read xlsx  file  .

[BrandonB · Accepted solution]

I mocked up a quick example of pulling this data out. Take a look at the attached workflow. There may be a more elegant solution but this is quick and gets the job done. 

  

[lucasyliu]

Excellent, thanks so much for the quick turnaround!

[lucasyliu]

Quick followup, I ran into another problem using actual data, where there is a header containing string "&" symbol. What is the proper way to handle XML with "&" symbol in Alteryx? Sample data attached.

                

I checked the "Ignore XML Errors and Continue" box to bypass the error, the result is the header "ItemA&E" is missing the "&E" portion.