Parse full XML

[sagar_agarwal]

Hi All,

I have browsed through different topics but unable to find what I am looking for - I have an XML file and want to parse it out completely. This means I don't wish to extract any particular field, but ALL the fields in the XML. This is because the XML has about 200+ tags and I am not sure what all fields would be important to me when I just open the XML in Notepad++.

Is there any way to achieve this?

[MarqueeCrew]

Did you try using the input data tool and then browse the data?

Sent from my iPhone

[sagar_agarwal]

Hi - apologies for the late reply.

I did try that first hand - but what it does is that it parses all the innermost childs. The data in my XML was structured upto 4 levels deep - with every level having same repeating tags but different data. So it became necessary for me to use the Select tool after every XML parse tool to rename the fields to make sense of which parent they are coming from. Apparently I had to parse every child, and the children within them individually.

Just a Q: In the XML parse where we can specify which child to parse, is there any way to specify multiple childs at the same time?

[serendipitytech]

Hi @sagar_agarwal

I think I have a similar scenario that I've been trying to workout. With some help here, I have a workflow that will list out all the tags used (as I didn't even know what names some sources used), but I'm still struggling with getting a usable output. You mentioned using a series of pars-select tools to get somewhere, would you be able to share your workflow perhaps? I'm curious what that setup looks like and would like to give it a try. 

The file I have is about 1.4GB, so an additional hurdle is that when I make a change and try it, it takes around 5 minutes to run, and depending on the options, the amount of spare disk space skyrockets and sometimes exceeds the 100GB I have available.

[SophiaF]

While my thought is that the ballooning is hard to avoid (try and optimize your field types), this macro on the Gallery may be able to get you the XML output that you need:

Parse XML

[serendipitytech]

Thanks for that link. 

So, I'm very new to this program, and haven't spent a lot of time with it quite yet. I'm looking at the downloaded workflow, but I'm unsure of how to get started with it. It has a Macro Input that is set to "Text input", if I change that to File Input and point to my XML the workflow fails, I then tried adding this workflow as a macro to an existing workflow, but it's not a macro itself. 

Sorry for being dense, but I'm just not sure how to utilize this file? Is it possible it just isn't compatible with the format of the XML file I have? The error I get is that at the first select option in the worflow "No valid fields were selected". All of the fields in the XML file I have are detected as "V_String" at input. 

I will try to reach out to the author of the Macro as well.

[SophiaF]

@serendipitytech - this is actually a complete Alteryx tool, so what you see is actually the "inner workings" of the tool. To use it as a standard tool in your workflow, first save it (without any modifications) somewhere on your machine (I would suggest creating a Macros folder and putting it in there). Then, in Alteryx, go to Options - User Settings -> Edit User Settings  and on the Macro tab, click the + and navigate to the folder you just created. You should now be able to find the tool in your toolbar under "Macros", or if you search it in the tool bar.

Then, bring in your XML file as a straight text file. You will want to configure the Input tool like this:

After that, connect your Parse XML tool and run!

[serendipitytech]

Thanks so much! Got it working after a few little bumps. The output is I think will be helpful in determining some of the pieces in the file for sure. 

[AshishD]

Hello @SophiaF, Thank you for the helpful post.

I am stuck in a similar situation and being a newbie unable to find a way to resolve it in Alteryx. Can you provide your input to this?

I am connecting Alteryx to fetch data using API. The API response is in XML format. There are 2 things which I want to do:

1. Each row from the output is having a column which is the API response data in XML format. There will be hundreds of such rows each having the data in one of the cell. How to parse each such cell?

2. The response will be of this format:

<?BrightCloud version=bcap/1.1?><bcap><response><status>200</status><statusmsg>OK</statusmsg><uri>http://www.sueddeutsche.de/thema/heuschnupfen/</uri><categories><cat><catid>63</catid><conf>85</conf...>

There can be multiple catid within the categories tag. How to parse this?

Column I in the attached file. Please provide your input.

Thank you,

Ashish

[aeulusan]

Hi Sophia,

i downloaded the macro and input my XML as a .csv file as you mentioned. However I cannot find the macro I downloaded even though I have downlaoded and put it in a macros folder. When I went to the Edit User Settings > Macro> + , I was only able to select the Macros folder and not the macro itself. can this be the problem?

Also, after the input as a .csv file, I ended up having a single column with all the tags inside as rows. Can you help why I am not able to open your macro? Thanks!