XML with an open-ended child parameter

I have XML files that I'm combining which has employee information and their dependents. From my input tool I can make a XML Parse which says:

Field with XML Data: Request OuterXML

Specific Child Name: Dependent 1

Return Child Values is checked.

I can make this work for Dependent2, 3, 4, 5 by making separate XML Parses for the specific children, but the problem is there can be an open number of dependents (Some families in this world can be pretty huge). The only way I've been able to get the results I want is to have an XML parse for each specific child name. There doesn't appear to be a way to say "I want all fields that begin with "Dependent" followed by a number.

Auto Detect Child doesn't appear to do the trick...

Common Use Cases

Accepted answers

cjames728

Found a better solution.

From the input data glyph, I have a separate XML Parse. Root, return child & outer XML. I then go into the developer tools and use the Dynamic select. Formula is (Left([Name], 9) = "Dependent" and right([Name],9) = "_OuterXML") or [Name] = "request_id" This weeds out the columns I want. I can then use a Transpose using request_id as a key and everything else selected. Then a filter to take out the null values and now I have something to run an XML Parse against (Root, Return child values)

All comments

BenMoss

Personally when working with xml files I prefer to bring them into Alteryx as undelimited .csv files. To me it is much clearer and easier to understand, and focus on specific elements you know exist.

Ben

unknown

Bring in the XML and on the full/outer XML field split to rows using the Text to Columns tool; the separator will be \n.

That will create a record for each line of XML. Then, you can filter out the rows with containing functions or starts with functions. Once isolated, using RegEx or cleanse those remaining records to get the result you need.

cjames728

Found a better solution.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703