ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Nested XML parsing problem

JFD
6 - Meteoroid

Hi,

 

I am trying to parse this xml file but is having a hard time doing so. It seems that multiple tables (country, subdivision, territories, categories) are all into 1 block. I tried to use the XML Parse, REGEX and straight input tools but my limited knowledge of Alteryx (2 months) seems to be a problem.

 

The goal here would be to extract the data from the xml to get relational tables between the different elements (countries, subdivisions etc.)

 

Much appreciated!

Thx!

Ben_H
10 - Fireball

Hi JFD,

 

I think maybe setting the XML Child Element Name to "country" (without the quotation marks) is what you need to do.

 

Just drag your sample data onto the canvas, in the config type in country for XML Child Element, then check the "Return Child Values" box.

 

Ben 

 

 

JFD
6 - Meteoroid

Thx 

 

It helps me a bit. I guess by using your suggestion I need to repeat the Input tool as many times as I need a section to be "carved out" meaning one for country, one for subdivision and so one. At least that is what I am doing now.

 

My problem now is the some child don't hold their parent id. This is the case for subdivisions and their respective country. This is also true for the subdivison name which seems to be in a different child than the subdivision itself!!!

 

In order to link the child and parent, the only thing I see would be to "Return Outer XML" and then use the XML Parse tool. Not sure if it makes sense.

 

Thx!

Ben_H
10 - Fireball

Hi @JFD 

 

I had another think about it - is the attached more like what you wanted?

 

Ben

danilang
17 - Castor
17 - Castor

hi @JFD 

 

Using a single input tool and subsequent XML parse tools is the way to proceed.  The attached workflow uses an input tool to import the file, then a separate XML Parse tool for each of the different lists in the different nodes

 

w.png

Using a single input tool keeps the parent_id at all stages.  For example at the subdivision languages level we have the id's of the country, subdivision and language.

 

An important thing to note here is the strategic use of the select tools.  As soon as you've extracted the child_outer_xml chunks, deselect the parent_outer_xml field.  This is parent_outer_xml is repeated for each of the records in your child record set, so memory use can balloon very quickly.

 

Dan 

JFD
6 - Meteoroid

Thank you very much 

 

 

Jude451
8 - Asteroid

Got it 

Labels