Biztalk mapper and Functoid (columns to rows tranformation)

This article demo the use of Table Looping and Table Extractor functoid to transform columns to rows. The scenario is there's a source schema which has 3 dates fields. These dates needs to be transformed into rows in the destination schema. A date type field in the destination schema will indicate the purpose of the date (Eg. An enum value).

1. Create an Integration Account project

2. Add the source and destination schema

3. Add a new Map

4. Open the map and select the source and destination schema

5. Add a "Table Looping" functoid to the grid

6. Drag the parent node to the functoid to connect them

7. Double click on the "Table Looping" functoid in the grid and enter the number of columns to generate (In my example, 2)

8. Then drag all the date fields to the table looping functoid

    *Double click on it will shows the input as shown below.

9. In my scenario, I'll need date type, so I'll be adding in 3 constant value as the date type (POD, DLD, DCD)

10. Click on the "Table Looping Grid" in the screenshot above and set the rows, then click "OK" to close it

11. Drag a line from the "Table Looping" functoid to the parent node of the date

12. Drag 2x "Table Extractor" functoid to the grid

13. Drag a line from the "Table Looping" functoid to the first "Table Extractor" functoid. Then drag another line from the "Table Looping" functoid to the second "Table Extractor" functoid.

14. Drag a line from first "Table Extractor" functoid to the "DateType" field.

15. Drag a line from the second "Table Extractor" functoid to the "DateValue" field.

      It should now looks like the screenshot below.

16. Double click on the first "Table Extractor" functoid and set "1" as shown in screenshot below.

This value indicate the table extractor should extract the 1st column in the Table Looping output. In our example, it extract the first column from the Table Looping functoid and map it to DateType.

Do the same setup to the second "Table Extractor" functoid by setting it to "2" (which indicate it should extract the 2nd column from the Table Looping output, which eventually mapped to "DateValue")

17. Run a test on the map with a sample input XML as shown in the screenshot below

18. The output will be as shown in the screenshot below