Combining nested loops into a simple loop

Convert an XML structure with nested loops into a flat CSV structure by combining nested loops.

Before you begin

You have created a map and added an input and an output structure.

About this task

In this example, the input XML file contains information about a purchase order. Each Address element contains an Items element, which contains at least one Item element. The goal of the transformation is to have a CSV file with a row for each Item. Each row should also contain the relevant shipping information.

The example uses the following XML sample as input:

<PurchaseOrderShipping PurchaseOrderNumber="99503" OrderDate="2011-03-15T12:10:03+05:30">
  <ShipDate>2011-04-30T23:50:00+05:30</ShipDate>
  <Address>
    <Name>Deandre King</Name>
    <Street>4894  Winding Way</Street>
    <City>Southfield</City>
    <State>MI</State>
    <Zip>48075</Zip>
    <Country>USA</Country>
    <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
    <Items>
      <Item PartNumber="872-AA">
        <ProductName>Lawnmower</ProductName>
        <Quantity>1</Quantity>
        <USPrice>148.95</USPrice>
        <Comment>Confirm this is electric</Comment>
      </Item>
      <Item PartNumber="926-AA">
        <ProductName>Baby Monitor</ProductName>
        <Quantity>2</Quantity>
        <USPrice>39.98</USPrice>
      </Item>
    </Items>
  </Address>
  <Address>
    <Name>Burl Clark</Name>
    <Street>3807 Pointe Lane</Street>
    <City>Fort Lauderdale</City>
    <State>FL</State>
    <Zip>33308</Zip>
    <Country>USA</Country>
    <Items>
      <Item PartNumber="356-KX">
        <ProductName>Gas canister</ProductName>
        <Quantity>1</Quantity>
        <USPrice>123.02</USPrice>
      </Item>
    </Items>
  </Address>
</PurchaseOrderShipping>

The output structure is defined as follows:

ItemsShipping
	item (0:*)
		PurchaseOrderNumber
		ShipTo
		ShipDate
		PartNumber
		ProductName
		Quantity
		USPrice

Procedure

Drag and drop the input PurchaseOrderNumber and ShipDate on the corresponding output elements.
Open the Functions tab and drag the Concat function on the output ShipTo element.
This function will be used to concatenate the different address elements from the input into a single output element.
Drag the following input elements on the Concat function in the Value tab of the ShipTo output element:
- Name
- Street
- City
- State
- Zip
- Country
Information noteTip: You can drop an element on Concat to add it at the top of the list, or you can drop it before or after another element.
Double-click the Concat function to define the string to add between the concatenated elements.
This parameter is empty by default. If you want the different parts of the address to be separated by a space for example, enter a space in the Join String field.
Drag the input PartNumber element to the corresponding output.
Since the output structure is already looping on the input Address element, this causes a conflict.
In the dialog box that opens, select Change the output map element looping and click OK.

When selecting this option, the output structure will loop on the input Item element. A context is automatically added to the output item loop.

As a result, if an Address element has multiple Item elements, the output will contain a row for each Item.
Drag the input ProductName, Quantity and USPrice elements to the corresponding output elements.

Results

Your mapping is configured. If you have a sample document, you can click Test Run to see the result. In this example, it should look like this:

PurchaseOrderNumber,ShipTo,ShipDate,PartNumber,ProductName,Quantity,USPrice
99503,Deandre King 4894  Winding Way Southfield MI 48075 USA,2011-04-30T23:50:00+05:30,872-AA,Lawnmower,1,148.95
99503,Deandre King 4894  Winding Way Southfield MI 48075 USA,2011-04-30T23:50:00+05:30,926-AA,Baby Monitor,2,39.98
99503,Burl Clark 3807 Pointe Lane Fort Lauderdale FL 33308 USA,2011-04-30T23:50:00+05:30,356-KX,Gas canister,1,123.02

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here