Version 22.0.8473

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

Once a template file has been set in both Source File and Destination File, the visual designer will populate with the complete document model for both the source and destination XML. These model can be traversed as an XML tree.

Parent and Leaf Nodes

The XML tree in the visual designer 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 will produce a corresponding destination element, including all of the destination element’s children.

Once a Foreach relationship is established, a green xpath will appear in the destination. Elements at this path in input XML will result in a new instance of the output node and its children. In a more technical sense, 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 will loop.

Dragging a source Leaf onto a destination Leaf will instruct 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.

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 reaching the current node.

Mapping Loops

Parent nodes (Foreach loops) should be mapped before Leaf nodes are mapped. 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:

Note 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 (i.e. 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 would still ensure that the appropriate number of customerInfo element groups appeared in the XML output.

Mapping Multiple Loops

XML Mappings will 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. Outer loops should be mapped 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 may repeat (and thus each requires a Foreach relationship): (1) a single report may contain multiple individual orders, and (2) an individual order may contain multiple line items.

Example Input

The source template structure may look like this:

<OrderReport>
  <WebOrder>
    <CustomerName>John Doe</CustomerName>
    <PurchaseDate>12/21/18</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 (like the MySQL Connector, SQLite Connector, CData Connector, etc), and the Template Files from Database Connectors section discusses 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 may 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 will 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 visual designer should look like this:

After the loop relationships are established and any unnecessary destination elements are removed, the Leaf elements can be mapped 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. This practice minimizes the performance impact associated with Foreach relationships.