Version 23.4.8839

• Parent and Leaf Nodes
  • Foreach Relationships
• Mapping Loops
• Unnecessary Parent Nodes
• Testing Mappings
• Mapping Multiple Loops
  • Example Input
  • Example Output
  • Establishing the Looping Relationships
• Best Practices

Once you have specified the source and destination template files on the connector Settings tab, the Mapping Editor populates with the complete document model for the source and destination XML. These models can be traversed as an XML tree.

Parent and Leaf Nodes

The XML tree in the mapping editor has two types of nodes:

• Parent nodes have children but no value.
• Leaf nodes have a value but no children.

Parent nodes in the source can be dragged onto parent nodes in the destination, and leaf nodes in the source can be dragged onto leaf nodes in the destination.

Foreach Relationships

Dragging a source parent onto a destination parent establishes a Foreach relationship between the source and destination nodes. This means that each occurrence of the source element produces a corresponding destination element, including all of the destination element’s children.

Once a Foreach relationship is established, a green xpath appears in the destination. Elements at this path in input XML result in a new instance of the output node and its children. In other words, a Foreach relationship instructs the connector to loop over a given xpath in the source and produce the mapped destination structure for each element it finds. The green xpath in the destination tree view is the xpath over which the connector loops.

Dragging a source leaf onto a destination leaf instructs the connector to populate the destination element with the value from the source element. After dragging-and-dropping onto the destination node, the xpath from which values will be read appears in the destination tree view, as shown in the image below.

The xpaths displayed in the destination tree view are either absolute or relative. Absolute xpaths begin with a slash (/) and describe the entire xpath in the source, beginning from the root of the document. Relative xpaths do not begin with a slash, and they are relative to a Foreach loop set in a parent node.

A relative xpath can be relative to multiple Foreach loops (as long as the element has multiple parents that each have a Foreach relationship mapped). To find the absolute xpath for any given relative xpath, simply concatenate each parent’s Foreach xpath, starting from the top of the document, until you reach the current node.

Mapping Loops

Map parent nodes (Foreach loops) before leaf nodes. Establishing the loop relationships requires an understanding of the source and destination XML structures: whenever a repeated element in the source should result in a repeated element in the destination, those elements should be mapped together in a Foreach relationship.

Within a Foreach loop, leaf element xpaths are relative to the mapped Foreach xpath.

As a very simple example, consider the following source and destination XML, where a nested XML structure should be converted into a flat XML structure:

<Source>
  <customer>
    <name>
      <first></first>
      <last></last>
    </name>
    <address>
      <streetLine1></streetLine1>
      <streetLine2></streetLine2>
      <city></city>
      <state></state>
    </address>
  </customer>
</Source>

<Destination>
  <customerInfo>
    <firstName></firstName>
    <lastName></lastName>
    <addressLine1></addressLine1>
    <addressLine2></addressLine2>
    <city></city>
    <state></state>
  </customerInfo>
</Destination>

Each repetition of the customer element in the source should result in a customerInfo element in the destination, so these parent elements should be mapped together to form a Foreach relationship. Then, the mapping for each Leaf element is simple:

Notice that the xpaths for the leaf elements are relative to the xpath in the parent element (the xpath that defines the Foreach relationship).

Unnecessary Parent Nodes

When establishing a Foreach relationship, only a single instance of the mapped elements needs to exist in the source and destination file. In other words, the Foreach relationship takes care of ensuring that the number of output elements matches the number of corresponding input elements.

To make this clear, imagine in the above example that the source file (the template for the input XML) had multiple sets of customer element groups. Establishing a single Foreach relationship between customer (source) and customerInfo (destination) ensures that the number of customerInfo element groups matches the number of customer element groups for any input file. Since it only takes one customer element to establish this Foreach relationship, all other customer elements are irrelevant to the mapping and can be ignored or deleted.

Similarly, if the destination file in the above example had multiple customerInfo elements, all but one should be deleted. The Foreach relationship between customer and customerInfo ensures that the appropriate number of customerInfo element groups appear in the XML output.

Testing Mappings

Use the Test Mapping button to check that your mappings work as you expect. The test populates the destination elements with values from the source files, as shown below:

If a test fails, the Destination pane provides an explanation of the failure.

Mapping Multiple Loops

XML Mappings often require multiple Foreach loop relationships within the same document. The principle for mapping loops remains the same: any repeated parent elements in the source XML that should generate repeated elements in the destination XML should be mapped as a loop. Map outer loops before inner loops (in other words, start at the top of the XML structure and work down). All looping relationships should be mapped before any leaf elements are mapped.

As a common example, consider mapping an incoming Purchase Order report to a destination database. Such a mapping contains two distinct element structures that might repeat, so each requires a Foreach relationship: (1) a single report might contain multiple individual orders, and (2) an individual order might contain multiple line items.

Example Input

The source template structure might look like this:

<OrderReport>
  <WebOrder>
    <CustomerName>John Doe</CustomerName>
    <PurchaseDate>12/21/22</PurchaseDate>
    <Line>
      <ItemName>Hammer</ItemName>
      <ItemCost>1500</ItemCost>
      <ItemQuantity>1</ItemQuantity>
      <ItemDescription>Standard claw hammer</ItemDescription>
    </Line>
    <Line>
      <ItemName>Nail</ItemName>
      <ItemCost>10</ItemCost>
      <ItemQuantity>20</ItemQuantity>
      <ItemDescription>Ten penny nails</ItemDescription>
    </Line>
    <Subtotal>1700</Subtotal>
    <TaxPercent>4</TaxPercent>
  </WebOrder>
</OrderReport>

This example includes only one WebOrder element for brevity, but the mapping should handle cases where multiple WebOrder sections are included in the same OrderReport.

Example Output

The output of this mapping should match the XML model of a database insert. The XML model of a database insert is created automatically by a database connector (for example, the MySQL, SQLite, and CData connectors), and the Template Files from Database Connectors section describes how these XML models can be used as template files in the XML Map Connector.

Proper database design suggests that the data should be inserted into two separate tables, one for Orders and one for Line Items. Generating an appropriate input mapping for this approach can result in a template structure like this:

<Items>
  <Order>
    <FirstName></FirstName>
    <LastName></LastName>
    <Date></Date>
    <OrderLine>
      <Name></Name>
      <Desc></Desc>
      <Price></Price>
      <Amount></Amount>
    </OrderLine>
  </Order>
</Items>

In this example, the Order element represents an insertion into an Orders table, and the OrderLine element represents an insert into a Line Items table. Templates are generated with only one element representing each table, but the Foreach relationships established during the mapping ensure that the appropriate number of inserts are created.

The other children of Order (FirstName, LastName, Date) represent columns in the Orders table, and the children of OrderLine (SKU, Price, etc) represent columns in the Line Items table.

Establishing the Looping Relationships

A WebOrder element in the Source should result in a new insert into the Orders table, so it should be dragged onto the Order element in the Destination. Similarly, a Line element in the Source should result in a new insert into the Line Items table, so it should be dragged onto the OrderLine element in the Destination.

After establishing these two Foreach relationships, the mapping editor should look like this:

After the loop relationships are established and any unnecessary destination elements are removed, you can map the leaf elements to fill in the destination values:

Best Practices

Foreach loops are a powerful tool for mapping repeated elements in a destination file. However, it is important to follow these best practices to ensure proper functionality and avoid performance issues:

• When using large XML files, avoid mapping the entire file as a Foreach loop. This style of mapping instructs the connector to load the entire file into memory, which can result in long processing times.

  

• Instead, it is best practice to keep the full source document structure in mind, and only create Foreach relationships for elements that are known to be repeated (as described above in Establishing the Looping Relationships). This practice minimizes the performance impact associated with Foreach relationships.