Creating Complex XML from Flat Data

Dataflows often produce records containing flat fields which get written to XML as a simple XML elements. If you want to organize flat fields into complex XML elements to produce hierarchical data, you can do so using one or more Aggregator stages.

For example, given this flat data where the first line is a header record:

addressline1,age,city,country,gender,name,number,postalcode,stateprovince,type
1253 Summer St.,43,Boston,United States,M,Sam,019922,02110,MA,Savings

You might want to group the fields of data related to the address and fields related to the account into complex XML elements named <Address> and <Account> as shown here:

<CustomerRecord>
        <name>Sam</name>
        <age>43</age>
        <gender>M</gender>
        <country>United States</country>
        <Address>
            <addressline1>1253 Summer St.</addressline1>
            <city>Boston</city>
            <stateprovince>MA</stateprovince>
            <postalcode>02110</postalcode>
        </Address>
        <Account>
            <number>019922</number>
            <type>Savings</type>
        </Account>
</CustomerRecord>
  1. Add an Aggregator stage to the point in the dataflow where you want to construct complex elements.
  2. Double-click the Aggregator stage to open the stage options.
  3. Select Group by and click Add.
  4. Select the field that contains a unique value for each record, such as an account number and click OK.
  5. If there are other simple fields you want to pass through, select Group by and click Add again and add all the simple fields you want to include.

    For example, in this case there are five simple fields that will be included in each record: number, name, age, gender, and country.

  6. Select Output lists and click Add.
  7. Select New data type. This will have the effect of defining a new complex element. Enter a description for the kind of data that this complex element will contain. For example, you could enter "Complex" since you are constructing a complex XML element. The data type name can be anything you want.
  8. In the Name field, enter the name to use for the field. This will also be the name of the XML element.
  9. Click OK.
  10. Select the field you just created and click Add.
  11. With Existing field selected, choose a field that you want to add as a child field to the complex element and click OK.
  12. Repeat the previous two steps to add additional fields to the complex element.
  13. Add additional complex fields as needed.

    When you are finished, you should have an Aggregator stage that lists each simple and complex field you want to include in each record. For example:

  14. Click OK.