Hierarchical to flat structures in Jitterbit Design Studio

Instance mapping

An "Instance" can be a row in a database table, or an XML element which might have a value or attributes or sub-elements. When the mapping of a target instance depends on possibly more than one instance of source, we call it "Instance Mapping". A good example is the mapping of a source represented by line items specifying the cost and quantity mapped to a target field which represents total cost. Another example is the mapping of a source that contains multiple instances of name value pairs to a target field that represents a username. "Instance Mapping" allows the user to define a mapping to reduce multi-instance source to a single target instance. It essentially allows the user to move row-oriented data into column-oriented data, with the number of records generated dictated by the level of the data being mapped. This can be demonstrated by Hierarchical to Flat Structures mappings, where you will need to resolve some information from one or more previous or "higher level" records.

Two sample situations are provided below to illustrate how to achieve hierarchical-to-flat transformations.

Sample situation 1

Consider the following situation, where a source XML document contains a looping structure with many levels, with a de-normalized (flat) target document. This could occur in other types of transformations, such as complex text-to-database, etc.

Document structures and data

• Source Document Structure: The source document is an XML document with many levels, some of which loop.

• Source Document Data: This file contains sample data that mirrors the DTD file's structure.

• Target Document Structure: The target document is a text file.

Scenario

The challenge in mapping the source and target documents is to correctly map the levels of the source with the levels of the target, making sure the correct data is placed into the target.

In this mapping scenario, a record for each of the item numbers needs to be created. To do so, an itemnumber in the source XML document must be mapped to the target's itemnumber column. When we do this, we are creating one (target) record for each (source) instockitem entry.

Next, the quantity of each source itemnumber must be mapped to the quantity field of the target. Once complete, the target would look like:

Map the Cost Attribute

To get the Cost attribute, map the attributevalue element of the attribute tag to the Cost field of the target. Since there can be more than one attribute code/value entry under the attribute tag, you must specify which attribute will map to the target's Cost field. Thus, you must define which exact attribute to use for the Cost field. In this example, it is the cost attribute code.

Now that we know what the options are, we need a way to define it. In this example, it would be to identify that we wanted the attributevalue where the attributecode is equal to Cost. It may also be that attributes are delivered in a certain order, and that their position determines the correct attribute instance.

Follow these steps to map Cost correctly:

• Select the [V] under the attributevalue element in the source pane.

• Click on Cost in the target pane.

• Click the Map icon.

• Click on the Formula icon to display the Formula Builder.

• The Formula Builder should appear. Open the Instance folder in the left pane, and select the FindValue() function.

• Click the Insert button to add this function to your expression.

• The new expression appears in the pane at the top of the dialog.

• You now need to correctly configure the function's parameters:

  • Carefully highlight the first <arg> in your expression at the top of the window. This parameter should be the name of the attribute, so type COST, replacing the <arg> in the expression.

  • This parameter provides the code used in the source data element. Making sure the expression's second <arg> is highlighted, replace it by double-clicking the attributecode's [V] data element.

  • The last parameter returns the source data element's value. Making sure the expression's last <arg> is highlighted, replace it by double-clicking the attributevalue's [V] data element.

• Compare your expression with the figure below, then click OK to close the window when you are finished.

Complete the Sample

Repeat the steps listed above to map the WEIGHT and COLOR attributes in the source to their target counterparts.

The final data output would look as follows:

You have completed the Sample 1 exercise. Compare your work to the figure below.

Sample situation 2

This sample is similar to that of Sample Situation 1, except the structure of the source document is a bit different. Also, instead of mapping the item information, we will be assembling records-based warehouse codes.

Document structures and data

• Source Document Structure: The source document is an XML document with many levels, some of which loop. Note that this DTD is different from the one in Sample Situation 1: In this DTD, the warehouse information is separated from the instock items.

• Source Document Data: This file contains sample data that mirrors the DTD file's structure.

• Target Document Structure: The target document is a text file. This target structure differs from the Sample 1 target because it uses a separate column to identify each warehouse.

Scenario

Similar to Sample 1, we want to create a record for each of the item numbers, but we want to have each of the warehouses be listed by columns in the target. To do so, we need to create one record for each instockitem entry in the source XML file. In this example, this will equate to 3 records (ABC-123, ABC-456, DEF-456).

We have attributes of instockitem, which contains the warehouses where the item is stocked (instockat). Within this attribute, there can be 0 or more entries to identify all of the warehouses where the item may be found, as well as the quantity in the warehouse. What we need to do is get all the ones for WH123, etc. into the proper column.

The logic for item number ABC-123 and warehouse WH123 would be:

Get the value of the attribute "quantity" of the "ABC-123" item where the attribute "whcode" is equal to "WH123".

For example, the answer to be supplied for WH123 for item number ABC-123 would be 450.

Follow these steps to correctly map and configure itemnumber:

• Map the source's itemnumber data element to the target's itemnumber.

• Map the source's itemquantity data element to the target's WH123 data element.

• WH123's data needs to be qualified. Select the target's WH123 data element and click the Formula button to launch the Formula Builder.

• The Formula Builder should appear. Open the Instance folder in the left pane, and select the FindValue() function.

• Click the Insert button to add this function to your expression.

• The new expression appears in the pane at the top of the dialog.

• You now need to correctly configure the function's parameters:

  • Carefully highlight the first <arg> in your expression at the top of the window. This parameter should be the name of the attribute, so type WH123, replacing the <arg> in the expression.

  • This parameter provides the code used in the source data element. Making sure the expression's second <arg> is highlighted, replace it by double-clicking the attributecode's [V] data element.

  • The last parameter returns the source data element's value. Making sure the expression's last <arg> is highlighted, replace it by double-clicking the attributevalue's [V] data element.

• Compare your expression with the figure below, then click OK to close the window when you are finished.

Complete the Sample

Repeat the steps you did above for the remaining two warehouses, WH456 and WH789, setting their values accordingly. You will then have completed the Sample 2 exercise. Compare your work to the figure below.