Have you run onto challenges when it comes to parsing XML files and trying to convert data to a ‘meaningful' tabular format? I had my share of fun and challenge. Especially if the XML is nested to multi-level. Analysis on data from XML files is one of the issues I have faced recently. The file (nodes and data) was next 13 levels deep with ~350 data fields to capture; and goal was to convert it to a tabular data and share on a SQL server to allow our partners to be able to use this data for different use cases including Quality Validation.
There are several macros available for parsing XML data in Alteryx community. I used Parse XML which is a powerful macro which can parse an XML file for all levels. This macro if used properly is awesome and reduces a lot of stress to deal with XML files.
I am going to show you an example where I will parse the "<body>" of the XML which generally has the most complex data. You can view another example of this on Alteryx Community by clicking here.
The example I am using is simple XML on 'Donuts' 😊
Every XML is different, but this macro will parse all nodes and data. After parsing, different techniques can be used to convert 'parsed' to a 'meaningful' data in tabular format.
I have provided some tips below on how to start parsing complex XMLs. A workflow is also attached to convert "Donuts.xml" to a tabular data format. If the data is required to be loaded to a Database server, I would recommend that you NORMALIZE data before loading. In the donuts example, you can have different tables for "Donut Master", "Filling Master", "Topping Master" and "Batter Master" and then 1 transaction file for "Donut Transactions".
Download the Parse XML macro here: https://gallery.alteryx.com/#!app/Parse-XML/584b5589f499c704689e363b
Credits:
Hildebrando Souza (https://community.alteryx.com/t5/user/viewprofilepage/user-id/51984)
Chris Smallwood (https://community.alteryx.com/t5/user/viewprofilepage/user-id/68765)
I have attached sample XML file, workflow and post (which includes steps and details)........looking forward to feedback and other ideas from friends on how they solved their XML file transformation issues.
Happy XML Parsing with Alteryx!
Pankaj
