Introduction

This article describes how to "flatten" an XML structure.

Why Is This Needed?

Tools that can consume and process XML are able to recognise most XML structures. However, some can only process up to a certain number of levels.

Additionally, if the records in the XML document are required by recordset-only consuming BPA Platform tools, available conversion tools can only convert flat XML structures.

Example XML Structure

Let's take the following XML:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
	<company>
		<name>Corkery, Graham and Kessler</name>
		<contact>
			<first_name>Joshuah</first_name>
			<last_name>Gregorin</last_name>
			<email>jgregorin0@narod.ru</email>
			<phone>+57-258-627-4614</phone>
		</contact>
		<address>
			<shipping>7 American Ash Court</shipping>
			<billing>9 Green Ridge Street</billing>
		</address>
	</company>
	<company>
		<name>Parisian LLC</name>
		<contact>
			<first_name>Natka</first_name>
			<last_name>Mochar</last_name>
			<email>nmochar0@hubpages.com</email>
			<phone>+62-816-119-6762</phone>
		</contact>
		<address>
			<shipping>98298 Kim Place</shipping>
			<billing>59124 Stuart Trail</billing>
		</address>
	</company>
	<company>
		<name>Spinka, Moen and Jakubowski</name>
		<contact>

and so on.

When you import this file into your task, the structure is recognised correctly — Import XML Document The Import XML Document tool imports an XML document to be used and processed by other task steps. To validate the XML documents at runtime, you can either import an XML schema file (XSD), infer from an existing XML document, or manually construct the XSD. tool > Main tab > OutputXML tab. However, when you use the Convert XML to Recordset The Convert XML to Recordset tool is used to convert XML data into a flat-format BPA Platform recordset, allowing the data to be used by recordset-only consuming tools. This could then be used to power alerts via fax, email, or SMS, create reports, power a workflow, perform FTP transfer, and so on. tool, the created recordset looks like:

The gaps in the recordset are NULL values. Each row in a recordset is treated as a new record by BPA Platform tools; according to this recordset, each company has two records. This may not be the result you want; ideally you want to create a "single record per company" recordset. You do this with the Transform Data Transform Data is a Format tool that enables a new structure to be created from an existing XML or recordset data source. The output generated by this tool is ready for use in a destination system which consumes or requires XML. tool.

Flattening the XML Structure

Using the XML example above, we use the Transform Data tool to flatten the XML structure to a single level. This can then be converted to a recordset structure later, if required, by the Convert XML to Recordset tool.

Do the following:

1. Import the XML file to the task using the Import XML Document tool.
2. Add a Transform Data step to the task and consume the output from the previous Import XML Document step.

3. Go to the Main tab.
4. Click Add Node and change the Name to the top-level element — in this example, we are using company.
5. Use Add Element to add the other XML elements, remembering that everything must be in the same level — you must create new elements for the child-level elements:

   

6. Go to the Mapping tab and map the original elements to new (click and drag one element to the other):

   

   You'll notice in the example above that the parent element company is also mapped. This forces the Transform Data tool to "step through" the input XML document to ensure that for every company record found, a mapped record is created in the output XML. Without this, only the first record encountered is outputted. When creating the mapping, a Simple function is automatically created — no further configuration is required for this.

7. Click OK to save the step.
8. You can now add a Convert XML to Recordset step to create the correct recordset then continue with the task.

The flattened recordset should now look like:

The "one record per company" recordset can now be successfully consumed and processed by other BPA Platform tools. Note how the column names are also prefixed with the parent XML node name too.