XML FILE READING

[jkorattiyil]

Hello, can someone please help me how can i read the attached xml file just like the xlsx attached? I needs to read from  <FilingBody FilingType="Location"> and <LocationAAde> should be base

[PhilipMannering]

@jkorattiyil Have you tried the XML Parse Tools and looked at the examples to try and understand how they work? What have you tried so far? Can you share your workflow?

[Rags1982]

It seems that there are errors in your xml file, this can cause issues when trying to read it. 

Usually, you could just use the xml parse tool as @PhilipMannering says, then specify the child node '<FilingBody FilingType="Location">', this should return the fields within that node. 

[jkorattiyil]

I am not familiar with XML files. Now i am just drag and drop the xml file to excel and save as a xlsx format. I am trying to see is there a way I can read the XML file just like the way how it will read if we drag and drop to excel and save as xlsx format.

[Rags1982]

I don't think you'd be able to save an Excel file like that. 

An Excel file is made up of many files. If you save a copy of the Excel file to a location, then change the name of the file to something like test.zip , you'll be able to then open that new 'folder' and see all the files within that xlsx file. 

Why are you trying to save it as an excel file? Do you have the data stored elsewhere in another file type? What's the overall plan of what you are trying to do? 

[jkorattiyil]

Can you try with the attached file ?

[jkorattiyil]

I am trying to read the xml file like a table based on the location number. If we drag and drop the xml file into an excel, then we can see the data is presenting like a table and each location code will list in one by one raw and associated data for each location codes.

[Rags1982]

Ok, so that xml file works without errors and I think I understand what you are trying to do now. Unfortunately, that is a really big job that I wouldn't have time to parse out right now. However, I will give you some tips: 

Bring the file in to Alteryx as an xml file

Open the file also in a program like Notepad++ (this will help you see the 'nodes')

Parse out the 'Root' using the xml parse tool. 

Now, each 'Node', or 'ChildNode' that you want is the headers in your Excel sheet. What you want to do is use the XML parse tool and enter the 'Specific Child Name'. These will be your Excel headers, but in the xml file they look like '<FilingBody FilingType="Location">' or '<Deductions>'

Once you parse out these headers, you will then see the values appear in your Alteryx workflow. 

You will need to parse out each header with an xml parse tool. Use select tools after each step to remove columns that you don't need as you will end up with hundreds of columns.