XML flattening like JSON Parse

[SeanAdams]

Hey all,

Given that the JSON parse is so good at flattening out data to make it easier to transpose - we're hoping to be able to either do the same with XML data to make it easier to parse in a single pass (currently the XML tool allows you to dig progressively into the tree in single steps but doesn't have a way to fully flatten in one pass like JSON parse does).

Have any of you built anything that simplifies the process of flattening out XML files like the JSON parse tool does (or a simple way to convert XML to JSON and then we can use the JSON Parse tool)

Happy to provide a worked example if that helps the discussion?

cc: @JoshKushner @Claje

[Jerod · Accepted solution]

The ODBC Driver for XML by CData (my employer) is able to parse nested XML documents and integrates seamlessly with Alteryx Designer.

For example, I've created an XML document based loosely on the MongoDB restaurants dataset (originally in nested JSON): 

Sample XML 

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <restaurant>
    <address>
      <building>1007</building>
      <coord>
        <long>-73.85608</long>
        <lat>40.848446</lat>
      </coord>
      <street>Morris Park Ave</street>
      <zipcode>10462</zipcode>
    </address>
    <borough>Bronx</borough>
    <cuisine>Bakery</cuisine>
    <grades>
      <grade>
        <date>1393804800000</date>
        <grade>A</grade>
        <score>2</score>
      </grade>
      <grade>
        <date>1378857600000</date>
        <grade>A</grade>
        <score>6</score>
      </grade>
      <grade>
        <date>1358985600000</date>
        <grade>A</grade>
        <score>10</score>
      </grade>
      <grade>
        <date>1322006400000</date>
        <grade>A</grade>
        <score>9</score>
      </grade>
      <grade>
        <date>1299715200000</date>
        <grade>B</grade>
        <score>14</score>
      </grade>
    </grades>
    <name>Morris Park Bake Shop</name>
    <restaurant_id>30075445</restaurant_id>
  </restaurant>
</root>

Set up the ODBC Driver

1. Set the URI property to the path to the XML file and set the XPath property to the XPath of repeated elements (the element you want to represent an individual entry).

   Set URI and XPath

2. Then set the Data Model property to FlattenedDocuments and set the Flatten Arrays property to the depth of arrays you want to parse. With these settings, each of the coord elements will be parsed separately, as well as each of the grades elements. In the case of the grades elements, an index will be appended to the "column" name for each element in the grades array.

   Set Data Model and Flatten Arrays

Add an Input Data Tool

1. In Alteryx Designer, add an Input Data tool, and set the File or Database to ODBC and select the DSN you configured.

   Adding the Input Data tool

2. Select the "table" you wish to connect to.

   Selecting the "table"

3. Run the Workflow to preview the data. You can see how the XML was parsed from the Metadata.

   Metadata for the parsed XML

   Below is a snapshot of the parsed data.

[SeanAdams]

Super response - thank you @Jerod - I'm going to play with this on the weekend!

Really appreciate all the time you took to craft such a detailed note!