Expanding Convert XML to Recordset functionality using the Data Transformation Component (DTC)

The TaskCentre Convert XML to Recordset tool was originally designed to process the XML output from any custom built ITSDK tool. It is for this reason it is unlikely to handle the more complex XML structures that could appear from the WSCT (Web Services Connector Tool).

The purpose of this article is to identify where users should use the Data Transformation Component (DTC) to pre-process XML prior to using the Convert XML to Recordset tool to produce a Recordset.

There are three scenarios covered in this article:

1. XML with Attributes

The Convert XML to Recordset tool does not handle an XML Document that contains Nodes or Elements with attributes. Attributes are often used to define Keys describing the data in the Element. Using Microsoft Exchange as an example we often see attributes used to indicate whether the Telephone or Address is a Business Address or a Personal Address.

Figure 1 below demonstrates the type of XML structure referred to in this example. Here we have three different elements all with different attributes. All three of the elements contain different data within both the element itself and the attribute.

Figure 1.

If the XML you wish to process resembles the structure shown in figure 1, then please proceed to the following knowledge base article for further details on the solution:

KB Article: How to Process XML with Attributes

2. XML with Elements with the Same Name

The Convert XML to Recordset tool does not handle XML containing multiple nodes or elements with identical names. See example in figure 2.

Figure 2.

If the XML you wish to process resembles the structure shown in figure 2 , then please proceed to the following knowledge base article for further details on the solution:

KB Article: How to process XML with Elements with the Same Name

3. Convert XML to RecordSet Null cells

When we process XML using the Convert XML to Recordset tool a recordset is produced which retains the XML structure. Depending on how we intend to use the recordset a structured recordset is not always a desirable outcome. Using the example XML in figure 3 below, the output from the Convert XML to Recordset tool will produce NULL values as in Table 1. Whereas what we really require is a flattened structure as in Table 2.

Table 1

Table 2

If you wish to process XML and produce a flattened recordset, then please proceed to the following Knowledge Base Article for further details on the solution:

KB Article: How to flatten an XML Structure

Figure 3.