This section describes some advanced options for mapping and provides some more details on how mappings can be done. Specifically some scenarios of advanced usage of the functoids in combination are described.
Mapping Optional Fields
If the source document of a map has an optional field in it that you are mapping to a field in the destination schema, the XSLT actually handles this for you and creates the destination field only if the source field exists. This way you won’t get an empty field in the output if the source field isn’t there.
This is achieved by wrapping the creation of the destination field in an if statement, as shown in Figure 3.16.
Figure 3.16 Resulting XSLT from mapping optional fields.
Had the OrderDate element in the source not been optional, the OrderDate field in the destination would always be created; and if the element at runtime had not been present, the output would have an empty element. The generated XSLT would be the same as shown in Figure 3.16 but without the enclosing if statement.
Note that if you try to map an optional field to a required field, you get a warning at compile time or when you validate the map.
The looping functoid can be used to give the compiler hints as to how to create the XSLT when you are mapping across different hierarchies in the source document. For example, consider the mapping in Figure 3.17.
The idea of the map is to create one AddressInformation record in the output for the ShippingInformation record in the source and one for the BillingInformation record.
The output, however, is shown in Figure 3.18.
This is clearly not the output you want, because the address information is mixed in one AddressInformation record. To fix this, you use the Looping functoid, as shown in Figure 3.19.
The output is now as expected, as shown in Figure 3.20.
Figure 3.17 Mapping across hierarchies without Looping functoid.
Figure 3.18 The output of the map when the Looping functoid is not used.
Note that in this example the BillingInformation record is mapped to an AddressInformation record before the ShippingInformation. This is because the order you add the inputs to the Looping functoid matters. If you add the ShippingInformation to the Looping functoid before the BillingInformation, the ShippingInformation is mapped first.
Figure 3.19 Mapping across hierarchies with Looping functoid.
Figure 3.20 The output of the map when the Looping functoid is used.
The Index functoid provides a means to retrieve a specific value at a specific place in a hierarchy of fields.
If you have the XML shown in Figure 3.21 and you want to get the value ItemNumber2 from the ItemNumber element in the second OrderLine in the first Order record, for example, you can use the Index functoid to do so.
This is done using the Index functoid as shown in Figure 3.22, with the parameters to the functoid being as shown in Figure 3.23.
Figure 3.21. XML example of the Index functoid.
Figure 3.22. Using the Index functoid.
The parameters are first of all the field to get a value from and then the index of each parent level from that field and up. So, the parameters shown in Figure 3.23 are for the second OrderLine in the first OrderLines in the first Order record.
Figure 3.23. Parameters for the Index functoid.
The Database Lookup functoids are used to look up values in a database if the data is not present in the source document of the map. This can be getting the price of an item or the address of a customer or anything else.
To explain the use of this functoid, consider the map depicted in Figure 3.24. You need to add the Database Lookup functoid first. This functoid takes four parameters:
- A lookup value. This is the value from the source document that you need to find information based on.
- A connection string to the database that holds the table you want to fetch information from.
- The name of the table you need to fetch information from. You can wrap the table name in square brackets ([ and ]) if you like (for instance, if spaces or reserved words are used in the table name).
- The column in the table that you want to match against the lookup value from the first parameter. This can also be wrapped in square brackets.
Figure 3.24. Using the Database Lookup functoid.
The functoid converts this into a SQL statement that looks like this:
SELECT * FROM <Param3> where <Param1> = <Param4>
In other words, it connects to the database using the connection string and then executes this SQL statement to get the row you need from the table you have specified.
The Database Lookup functoid returns only one row from the database. If the SQL statement returns more than one row, which is the case when the column specified in parameter four isn’t a unique key in the table, the functoid just returns the first row from the data set returned by the SQL statement.
Because the Database Lookup functoid returns an entire row, you need to extract specific values from this row. In the example in Figure 3.24 there are eight Value Extractor functoids that will each extract a specific value from a column in the row.
A Value Extractor functoid takes in two parameters:
- The Database Lookup functoid that returns the row to extract a value from. Note that although the Database Lookup functoid is the only allowed input, the user interface allows you to use any Database functoid as the input for the Value Extractor functoid. This returns in runtime errors, however, so get this right at design time.
- The name of the column to extract. Note that this cannot be enclosed in square brackets (as you can with the table name and column name for the Database Lookup functoid, as discussed previously).
The Scripting functoid is used for two main things:
- To perform some task that the built-in functoids cannot do for you and which isn’t needed often enough to justify developing a custom functoid. An example of this could be to generate a new globally unique identifier (GUID) or to do string replacement.
- To perform some task that the built-in functoids can do for you but that requires some combination of functoids that is too tedious to build. An example of this could be if-then-else functionality, which is described later in this section.
After dragging the Scripting functoid onto the map, you can double-click it to get to the functoid configuration window. Go to the Script Functoid Configuration pane, shown in Figure 3.25, where you can change the script.
In this screen, you may choose what language to use for your script. If you choose External Assembly, you can choose to call a public static method of a public class that is in a current assembly. The other five options allow for editing of the script to include in the map. For each, you can choose to import the script from an existing file by clicking the Import from File button. This is often a good idea because the editing window in the Script Functoid Configuration doesn’t have IntelliSense, syntax highlighting, or even allow for the use of tabulators to indent code.
Figure 3.25. Scripting functoid.
The types of scripts that take parameters all take in strings. Therefore, you cannot send a node from the source document as a parameter and expect to treat it as an XmlNode. You only get the value of the node connected to the Scripting functoid.
Take care that the number of parameters your script takes matches the number of parameters you have provided to the functoid.
If you choose to leverage the power of one of the two XSLT scripting options and connect the Scripting functoid to a field in the destination schema, you take responsibility for creating the entire structure of the field you have connected the functoid to and any children of this field. The other four scripting types can output only a string, which is then copied to the field the Scripting functoid is connected to.
The XSLT scripting functoids are especially useful for performing tasks that deal with the source XML structure, because you have access to the entire source XML using XPath in the script, which you do not otherwise. Also, because you need to generate XML structures in the XSLT functoids, you have the possibility to create XML nodes for which there was no data support in the source XML. Assume, for instance, that you need to add an order line to all incoming orders that adds shipping expenses. You would need to copy all order lines from the incoming order to the destination document but also create a new order line to add to the existing ones. This is only doable in custom XSLT, be it either in a Scripting functoid or in a custom XSLT script that you use in your map instead of leveraging the Mapper.
Note that for XSLT you do not have access to all the nice features and functions of XSLT 2.0, because BizTalk only supports XSLT 1.0.
You can use the output from one functoid as the input for another functoid. This is useful for building functionality that doesn’t exist in the built-in functoids.
If you want to make sure that a string in the input is trimmed for leading and trailing whitespace and also in uppercase, you need the functoids shown in Figure 3.26.
Figure 3.26. Functoid collection to trim input and convert it to uppercase.
Combination of Functoids for If-Then-Else
The built-in functoids provide you with two Value Mapping functoids that basically return their second parameter if the first parameter is true. This allows for an if-then solution, but there is no intuitive way of doing an if-then-else solution (that is, returning a third parameter if the first parameter is false). To build an if-then-else solution, you must use several functoids, as shown in Figure 3.27.
Figure 3.27. Performing the if-then-else logic.
The solution provided in Figure 3.27 is used to send the current date to the OrderDate field if the OrderDate provided in the input is empty. This is a case where the sender has a bug in his system that makes him send an empty element from time to time. The solution is to first use an Equal functoid to check whether the string equals the empty string and use the output of this comparison as the first input for a Value Mapping functoid that takes in the current date as the second input. So if the field is empty, the current date is mapped. The output of the Equal functoid is also used as input to a Logical Not functoid, which negates the input. This is then used to allow another Value Mapping functoid to output the value of the OrderDate in case the Equal functoid did not return true, meaning the string wasn’t empty.
Create Separated List
Assume that the order from a customer can have many Comment fields, but the internal order format allows for only one Comment field. In this case, you might want to concatenate the Comment fields from the input into one string for the output and separate the comments by some separator. Figure 3.28 shows an example of how to do this.
Figure 3.28. How to create a separated list of strings.
The functionality is built up using string functoids and one cumulative functoid. First, the input is concatenated with the separator. The output of this is sent to the Cumulative Concatenate functoid, which will then have the complete list as its output, with a separator at the end. This final separator is removed by using the String Extract functoid, which takes in the concatenated string as its first input. The second is the constant 1, and the third parameter is the length of the concatenated string minus 1.
Table Looping Functoid
The table looping functoid is useful to combine constants and fields from the source document into structures in the destination document. Let’s revisit the challenge faced that was solved using the Looping functoid, as shown in Figure 3.19. Often, schemas that share a record for different addresses have a qualifier on the record, which contains information about what type of address the current record contains. So, the destination schema would probably be as shown in Figure 3.29.
Figure 3.29. The map to implement before adding the Table Looping functoid.
The Table Looping functoid is used to build a table of information and then output each row in the table on at the time, thereby creating records in the destination schema. To implement the mapping in this example, first drag the functoid to the grid, and then drag a scoping record from the source to the functoid. In this case, that is the root node because that is the node that encompasses all the needed fields. The second parameter to the functoid must be a number indicating how many columns should be in the table. The third parameter and all the next parameters are values that can be used to build the table. As shown in Figure 3.30, all eight fields with information have been added as inputs to the functoid. Also, two constant strings have been added as parameters to the functoid (namely, the strings Bill and Ship). These two strings are the qualifiers used in this example, meaning that the AddressInformation record in the output that contains the shipping information must have a Type attribute with the value Ship and the other must have a Type attribute with the value Bill.
Figure 3.30. Using the Table Looping functoid.
To tell the compiler which record is to be created for each row in the table, you must drag the output of the Table Looping functoid to the record.
After doing this, you can start building the table, which is done either by double-clicking the functoid and then switching to the Table Looping Grid pane or by right-clicking the functoid and choosing Configure Table Looping Grid. This opens the table grid with as many columns as you have indicated by the second parameter to the functoid. You can now use the drop-down in each field to select which of all the parameters to the functoid to use for which columns in which rows of the table. The resulting table might look like Figure 3.31.
Figure 3.31. The table grid of the Table Looping functoid.
To determine which columns of the table go into which fields in the destination schema, you need to use the Table Extractor functoid. Add one of these, as shown in Figure 3.30, for each field to copy values into, and use the Table Looping functoid as the first input to each of them. The second parameter to each Table Extractor functoid must be the column number to extract. So, given the rows shown in Figure 3.31, let the first Table Extractor functoid have a second parameter of 1 and let its output go to the Type attribute of the AddressInformation record. Now configure the remaining four Table Extractor functoids to extract the correct column and map it to the correct field in the destination schema.
The resulting XML from testing the map should look as shown in Figure 3.32.
The grid configuration screen shown in Figure 3.31 has a check box at the bottom that can be checked to instruct the map that the data in the first column should act as a condition that specifies whether each row is created in the output. At runtime, the value of the first column is evaluated, and if data is found, the Value Extractor functoids associated with that row are called and the output record is created. If no data is found, the Value Extractor functoids are not called, and the record is therefore not created in the output. If the input for the first column is a Logical functoid rather than a field, the output record is created if the value from the Logical functoid is true (and not otherwise).
Figure 3.32. Result of using Table Looping functoid.
Conditional Creation of Output Nodes
You might sometimes want to implement conditional creation of output records. For instance, consider the opposite mapping of the one found in Figure 3.30. In this case, you will want to create a ShippingInformation record in case the Type attribute of the AddressInformation record has a value of Ship and similar with the billing information. This is achieved using logical functoids.
Logical functoids have a side effect to just being able to do logical operations. If you connect a logical functoid to an output record, the output record is only created if the functoid returns true. This means that the reverse mapping of the address information can be solved, as shown in Figure 3.33.
Figure 3.33. Conditional creation of records.
In this sample, the upper Equal functoid has a second parameter with a value of Ship, and the ShippingInformation record is therefore only created when the Type attribute has this value. The same applies for the BillingInformation, which is only created when the Type attribute has a value of Bill.
There are mappings that the Mapper cannot create for you using the built-in functoids. In that case, you have two options:
- Extend the map with Scripting functoids that perform the functionality you cannot accomplish with the built-in functoids.
- Create the map in the Mapper but don’t use links or functoids. Instead, use a custom XSLT script for the functionality of the map.
Also, the XSLT generated by the Mapper is as good as it gets when things are automatically generated. If you know what you are doing, you can usually create yourself more efficient XSLT that performs better. If you have performance issues with your map, you might therefore also choose to write a custom XSLT script and bypass the Mapper.
If you choose the option of creating an entire XSLT script to use as the map, you need to use the Custom XSL Path and the Custom Extension XML properties of the map, as explained earlier. After creating your custom XSL and possibly a custom extension XML, you need to specify the path to the XSL and extension XML in the two properties. This effectively bypasses anything you might have done inside the Mapper.
The extension XML is some XML that is used to create a link between a namespace prefix that you can use in your custom XSLT and an external assembly that contains methods you want to call. Just as with the XSLT, you can get an example of a custom extension XML file when validating your map. You get an extension XML during this process only if you are actually calling an external assembly from the map you are validating.
Often you need to translate some numbering scheme into another during the execution of a map. An example of this is when mapping between customer order formats and your own format or between your own format and the format of your supplier. In this case, you might have an item number in your internal ERP system, and the customers and suppliers have their own numbers. The cross-referencing functoids help you achieve this. The setup is fairly simple: You create some XML files that contain the information about trading partners and the numbers that need to be translated and then you use an import tool to import this XML into the cross-referencing-specific tables (xref_*) in the BizTalkMgmtDb database. The functoids can then be used to extract values from the tables.
This section contains a simple example, covering the scenario of item numbers that need to be translated from the numbers the customer uses to the numbers FineFoods uses. This scenario leverages only half of the XML documents that can be used for other cross-referencing features. For a complete description of the other XML files, refer to http://msdn.microsoft.com/en-us/library/aa578674(BTS.70).aspx.
This scenario leverages five XML documents. The first one is just a container that contains links to the other four. The XML files can be seen in Listing 3.2, Listing 3.3, Listing 3.4, Listing 3.5, and Listing 3.6.
Listing 3.2. Contents of Cross-Referencing Setup File
<?xml version="1.0" encoding="UTF-8"?> <Setup-Files> <App_Type_file>C:\CrossReferencing\ListOfAppType.xml</App_Type_file> <App_Instance_file>C:\CrossReferencing\ListOfAppInstance.xml</App_Instance_file> <IDXRef_file>C:\CrossReferencing\ListOfIDXRef.xml</IDXRef_file> <IDXRef_Data_file>C:\CrossReferencing\ListOfIDXRefData.xml</IDXRef_Data_file> </Setup-Files>
Listing 3.2 shows the contents of the setup file, which is really just a collection of links to the XML documents that contain the actual data that should be imported. Other than the four shown references, four other XML documents can be specified:
These are not covered in this simple scenario.
Listing 3.3. Contents of App_Type_file
<?xml version="1.0" encoding="UTF-8"?> <listOfAppType> <appType> <name>ERP</name> </appType> </listOfAppType>
Listing 3.4 shows the contents of the App_Type_file XML document. It is basically a list of application types that can exist. You can use any string you want. For this scenario only the application type ERP is used.
Listing 3.4. Contents of App_Instances_file
<?xml version="1.0" encoding="UTF-8"?> <listOfAppInstance> <appInstance> <instance>ERP_C1702</instance> <type>ERP</type> </appInstance> <appInstance> <instance>ERP_Internal</instance> <type>ERP</type> </appInstance> </listOfAppInstance>
Listing 3.5 shows the contents of the App_Instances_file XML document. It is a list of instances of the application types from the App_Type_file XML document. The XML in Listing 3.4 has two instances of the ERP type, namely the ERP system from the customer and the internal ERP system.
Listing 3.5. Contents of IDXRef_file
<?xml version="1.0" encoding="UTF-8"?> <listOfIDXRef> <idXRef> <name>ItemID</name> </idXRef> </listOfIDXRef>
Listing 3.5 shows the contents of the IDXRef_file XML document. It is a list of types of IDs that need to be translated. In this scenario, we need to translate identifications of items, but this can be any string you specify.
Listing 3.6. Contents of IDXRef_Data_file
<?xml version="1.0" encoding="UTF-8"?> <listOfIDXRefData> <idXRef name="ItemID"> <appInstance name="ERP_C1702"> <appID commonID="ITEM1">123</appID> <appID commonID="ITEM2">456</appID> <appID commonID="ITEM3">789</appID> </appInstance> <appInstance name="ERP_Internal"> <appID commonID="ITEM1">4301</appID> <appID commonID="ITEM2">4398</appID> <appID commonID="ITEM3">5432</appID> </appInstance> </idXRef> </listOfIDXRefData>
Listing 3.6 shows the contents of the IDXRef_Data_file XML document. It is the actual values that can be translated. In this scenario, the value 123 as an item identification from customer C1702 is translated into 4301, which is the corresponding item identification in the internal ERP system.
The functoids used to do the translation are shown in Figure 3.34.
Figure 3.34. Using the cross-referencing functoids.
Figure 3.34 shows how to translate the item identification from the ItemNo field in the order from customer C1702 to the ItemNumber in the destination schema. The ItemNo field is mapped to a Get Common ID functoid, which has these three parameters:
- The constant string ItemID, which matches the type of ID to convert, as specified in the IDXRef_file XML document
- The constant string ERP_C1702, which matches the application instance of an ERP system, as found in the App_Instances_file XML document
- The ItemNo field
The functoid uses the value form the source to retrieve the common ID for this application-specific ID. For instance, a value in the source document of 123 returns a common ID of ITEM1. This value is then passed into a Get Application ID functoid, which also has three parameters. The first parameter is the same as for the Get Common ID functoid. The second is the constant string ERP_Internal, which tells the functoid to get the ID for this particular application instance. The third is the output of the Get Common ID functoid. For this scenario, the third parameter has a value of ITEM1, and the output of the functoid is the string 4301.