How to flatten an XML Structure

Introduction

If you are viewing this article it means you wish to flatten an XML structure that would otherwise result in undesirable Null values when its converted into a recordset.

The sample XML we wish to flatten is shown in figure 1.

Figure 1.

In the task browser you will notice that the elements appear as expected however when it comes to processing them with other TaskCentre tools then the null values which result are not always desirable. Table 1 shows the result of processing the data in figure 1 using the Convert XML to Recordset tool and subsequently using it to feed other tools such as the Format As HTML tool. If you would prefer this structured recordset to be flattened so that the related customer name and address data appears one one row (Flattened) then follow the solution described below.

Table 1

Solution Description

Step 1

Using the Web Service Configuration we created previously create a new Multi Message Operation (MMO). We will not need any Single Message Operations as we are only going to be using the Data Transformation Component (DTC) to transform the data and not communicating with any Web Service. In this example we have called the new operation ‘FlattenXML’.

Step 2

Double click on the Input section to begin configuring an Input Data Structure to include all the desired elements.

In the example XML used here, we are interested in the Shipping and Billing Address nodes. It is important to include a Unique Identifier in order to maintain continuity with the original data set, in this case, we will use the ‘OrderId’ element. Figure 2 refers.

Figure 2.

Step 3

Now double click on the Output section to begin configuring the Output data structure. Here we will create an Output data structure which resembles the Input data structure, see figure 3.

Figure 3.

Step 4

Now that we have created a suitable set of Input and Output Schemas to hold the data we can configure to the mapping screen. In this case we simply map the relevant Shipping and Billing input and output structures, see figure 4.

Figure 4.

Step 6

We now return to the Task design and insert a new Web Service Connector Step, selecting the new MMO we have created and using the same Document Source from the Import XML Document tool.

Step 7

We will now need to configure the mappings for the task step. We select the original structured XML as our input source as well as the Configuration and MMO we have just created. Once we have moved over to the mapping tab we are able to see the input data structure on the left hand side and the data structure we created on in the MMO on the right. Simply map the required parameters. Figure 5 shows an example mapping.

Figure 5.

When the user consumes the output from this tool step using the Convert XML to RecordSet tool the structure will now be flattened as in example Table 2

Table 2

Compare this with the output from the Convert XML to Recordset tool which has not been through the flattening process – Table 3

Table 3

The XML has been successfully flattened using an MMO.

Please refer to the e-learning If you need help setting up loops to iterate through a recordset: Learn about: Transform Functions – Simple Looping