IMPORTING XML FILES WITH MANY NODES

[ericknvs]

Hi, everyone. 

I have an XML file with several distinct nodes which I am not able to import using the tool "Input Data".

When I take a look in the system, the original table has 16 columns. 

The only available format for download is XML.

However, once it comes to Alteryx, the importation doesn't work correctly. 

Likewise, Alteryx only returns the information contained in the header.

Not being possible to import the information from the inside nodes.

As presented in the following image, the highlighted line in yellow is the only data which I can successfully import.

First, let me say I already tried to build a flowchart with the "XML PARSE" tool, but the problem begins one step before, which means, in the "Input Data" Tool.

There is a copy of the XML file in the attachments. 

Thanks!! 

[koppenhe]

If you scroll down on the input tool option there should be options for the child values which you may need to enable to get the results expected. 

[ericknvs]

Yes, I tried it before. Even when I check the box "return child values"

All I get is ... 

This doesn't make sense at all. 

The information, the columns, and the fields should not be like this.

Do you know any other way to solve this issue? Tks in advance

[apathetichell]

the best way to do this is to parse it as text using regex and text to columns etc. It's painful and not easy.

[ericknvs]

Here is my workflow in case you find what is going on. 

I built three different containers so I could understand each step separately. 

Tks

[koppenhe]

I’d agree the next best action is importing as a text file. A previous community post references how to do this here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Parse-full-XML/td-p/38083

[gyang3]

@ericknvsyou're on the right track here with your current workflow. XML would typically have multiple elements that you need to parse out using the XML parse tool (root, child, grandchild elements). Sometimes, this involves multiple XML Parse tools to extract the multiple elements to get the data that you need.

I've attached a workflow just to show you what that may look like. Feel free to tweak it depending on what data you need.

If this helps to solve your issue, please mark as solved.

[danilang]

Hi @ericknvs 

For more examples of the technique that @gyang3 is demonstrating, check out Weekly Challenge-116 A Symphony of Parsing Tools.  It's input is a large, complex XML file. The file is large enough that using a straight linear expansion, like in @gyang3's example, causes your memory use to explode to the point where the workflow would take days to run.  The submitted solutions demonstrate how to parse the required elements and also remove the resulting xxx_Outer_XML fields to keep your memory use in check.

Dan

[Felipe_Ribeir0]

Hi @ericknvs 

I understand that the best and right way to do this is to just configure the XML Child Element Name on the input tool. For this file, it is prod.

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[ericknvs]

Well, actually these solutions only work partially, because there is a lot of lost information during the process.

Both solutions do work, but they do not attend the main purpose entirely, which is the full importation of an electronic invoice.

At a first moment, the configuration is to input the root element and the outer XML. @gyan3 and @danilang are both correct and the setup of multiple XML Parse tool might work outstandingly, except for the Text to Columns which makes a secondary role in this workflow and may be removable.

We have an experienced Tech Consultant in the team who found a way out of this problem and brought this flow. Additionally, it has been the best automation so far, with a great scalable potential to any other eventual e-invoice importation

This is the setup for the Input Data tool:

The full version:


created by: @JhonatanVLopes

So far, the answers you guys left still need more development because some gaps can be found on the flowcharts, regarding the main concepts of Data Visualization and the insights promotion to create value for whoever is behind the data analysis/management.

As you can see, for instance, fundamental information like address, delivery taxes, and interests are discarded in this way.

The next screenshot is a sample of the expected results.

Note the number of fields and records.

I appreciate your efforts anyways.

Tks