BizTalk Repeating Structures Table Looping and Table Extract

This article will show you how to use the Table looping functoid in conjunction with the Table extractor functoid. Together these functoids can be used to take input from a flat source file and create a repeating xml structure in a destination document.

What we will build
This is a simple example containing:

Two schemas (source and destination)
One Map
One Orchestration containing:
A Receive port
A Receive shape
A Construct message shape with a Transform inside it
A Send shape
A Send port
One source file
One destination file
Input and output directories on your file system

Pre-requisites
It is assumed you have a working knowledge of BizTalk 2006, maps, schemas and orchestrations. As such some instructions for creating BizTalk artefacts will be shorthand.

Create the source schema

Open Visual Studio and create a blank BizTalk project. Add a schema, call it MovieIcons.xsd and build it so as to resemble the schema in the following screenshot.



Adjust the properties so that they are as in the following screenshot



Select the MovieIcons root node and adjust the properties as follows:



Select the Icons node and adjust the properties as follows



Save the schema.

Create destination schema
Add a schema, call it MovieIconsRepeating.xsd and build it so as to resemble the schema in the following screenshot



Select the root node and adjust the properties as shown in the following screenshot


Next, select the MovieIconsRepeating node and adjust the properties as follows


Select the IconNames node and adjust the properties as follows


Save the schema.

Create the map
We will now create the map that will utilize the schemas

Right click on the BizTalk project and add a map artefact calling it TableLooping.btm.

Add schemas to map
Click the ‘Open source schema’ link on the left and navigate to the MovieIcons.xsd file. Next click the ‘Open destination schema’ link on the right and navigate to the MovieIconsRepeating.xsd.

Add functiods to map
We will end up with a map looking like the one below


From the toolbox, expand the Advanced Functoids tab and drag a Table Looping functoid onto the map surface. Connect the nodes from the source schema to it by clicking on each node in turn and dragging a line to the functoid.

Label the connections
Once all the nodes are connected, select each connection in turn and examine its properties.

Add values to the label properties of each node as follows

Node Label
Icons Icons
FirstName1 FirstName1
LastName1 LastName1
Etc.. Etc..
LastName3 LastName3





Double click the Table looping functoid to open up the Configure Functoid dialog.  You will need to add a single input parameter. Immediately below the Icons parameter, add a new one with the value of ‘2’ just as the following screenshot shows.



The ‘2’ parameter indicates that there will be 2 columns I the destination schemas repeating structure.

Add the Table Extractor functoids

Drag two Table Extractor functoids onto the map surface and join them up to the Table Looping functoid and the destination schema nodes as shown below



Double click on the Table Extractor functoid connected to the FirstName node of the destination schema. Add a second input parameter with a value of ‘1’ as in the following screenshot



This indicates that FirstName will go in the first column of the repeating structure.

Repeat this step for the second Table Extractor functoid, making the value of the added parameter ‘2’, indicating LastName will go into the second column of the repeating structure.

Now select the Table Looping functoid again and examine its properties. Select the ‘Table Looping Grid’ property and click on the ellipsis but to show the Configure Table Looping Grid Dialog. Adjust the values in each row for each column as shown in the following screenshot



Click OK and save the map.

Create the Orchestration
We will create an orchestration looking like the one below


Add a new orchestration to the project and call it TableLooping.odx. To the orchestration, add the following:

Add a Message called Message_In with the following properties



Add a Message called Message_Out with the following properties



Add a Receive port with the following properties



Add a Receive shape with the following properties



Add a ConstructMessage shape with the following properties



Add a Transform shape inside the Construct shape. Double click on it to open the Transform Configuration dialog. Select ‘Existing map’ and in the dropdown select ‘TableLoopingMap.TableLooping’ . Configure it so the ‘Transform Source’ is the Message_In and the ‘Transform Destination’ is the Message_Out

Add a Send shape with the following properties


Add a Send port with the following properties



Create Input and Output directory on file system
On your file system create an ‘In’ directory and an ‘Out’ directory. These directories are referenced in the Receive and Send ports you just created. To test this map you will drop a test file conforming to the MovieIcons schema into the ‘In’ directory and it will then be transformed by our map and appear in the ‘Out’ directory.

Create an input file
The easiest way to do this is to simply right click on the MovieIcon.xsd file in Visual studio and select ‘Generate Instance’. The output window will indicate where the file has been generated.

Open the file in the text editor of your choice and edit it so as to resemble the file below

<ns0:MovieIcons xmlns:ns0="http://TableLoopingMap.MovieIcons">
<Icons>
<FirstName1>Marilyn</FirstName1>
<LastName1>Monroe</LastName1>
<FirstName2>Cary</FirstName2>
<LastName2>Grant</LastName2>
<FirstName3>Charlie</FirstName3>
<LastName3>Chaplin</LastName3>
</Icons>
</ns0:MovieIcons>

When you drop this file into the ‘In’ directory a file will appear in the ‘Out’ directory named <GUID>.xml

If you open this file you will see something like the file below.

<?xml version="1.0" encoding="utf-8"?>
<ns0:MovieIconsRepeating xmlns:ns0="http://TableLoopingMap.MovieIconsRepeating">
<IconNames>
<FirstName>Monroe</FirstName>
<LastName>Monroe</LastName>
</IconNames>
<IconNames>
<FirstName>Cary</FirstName>
<LastName>Grant</LastName>
</IconNames>
<IconNames>
<FirstName>Charlie</FirstName>
<LastName>Chaplin</LastName>
</IconNames>
</ns0:MovieIconsRepeating>


As you can see the map has taken the flat format of the input file and built a repeating <IconNames></IconNames> structure in the output file.

This article has shown how to use the Table Looping functoid with the Table Extractor functoid to create a repeating structure in the destination file from a flat structure in the source file. This is a simple but powerful technique showing how flat files can be manipulated into a more versatile xml structure