Dynamic Workflow for Incremental XML Data Updates

[aglucas1229]

Hi Community!

We are receiving an XML data set from a vendor via FTP on a nightly basis, that I'd like to setup as a Dynamic Workflow to schedule via Desktop Automation on a daily basis.  Because it is in XML format, there are a lot of steps for parsing the data. The issue is that the data set is an incremental update - meaning that it will only includes fields and data that have been populated in their system on that day.  So, while a full record may have Columns A, B, C, D, and E, the incremental loads may only contain columns A, C, and E (like what I did there, ACE's?).  My workflow includes steps to parse and transform most/all columns - whether they exist in the incremental data set or not, as we'll never be able to predict what will come over each day. Will it be A, B, C? A, B, E? D, E?   Either way, we have steps/tools for all fields...that end up getting joined together to eventually be loaded into our final Snowflake table (using the 'insert if new' command).  And, as you can expect, if a tool is trying to call a field that isn't there, it will error out, and the workflow won't continue past that point.

So, my question to the community is - what can I do in this scenario if I want the workflow to keep running, even if a field isn't present in that day's data set?

While you all mull that over, I will try to create a sample of the workflow to share with my question.

[TrevorS]

Hello @aglucas1229 
Thanks for posting to the Community!
Were you able to provide a sample workflow and some sample datasets?

As a starting point, if you are able to include within your parse statements a placeholder for the missing fields, that will allow the data to upload even if the information is missing.

Once you have some sample data, the community will be able to better troubleshoot this for you!

Thanks,
TrevorS

[KilianL]

Hi @aglucas1229 ,

We got an XML parse Macro on the public Gallery which can parse out all nodes in your XML.

With this you might be able to reduce your parsing streams into one and make it dynamic.

You can find it here: https://gallery.alteryx.com/#!app/Parse-XML/584b5589f499c704689e363b

Example result

You can find an example attached where I dynamically parse an XML file up to 4 node levels.

It needs to be slightly adapted, but should work on most files.

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx