BizTalk Mapping. Using the Database Lookup Functoid

Introduction

In this article, I will show how to use the Database Lookup functoid to map incoming XML node to a database table column whose result value is set to a target XML node. This is mainly used because the data that is stored in the database table is dynamic, and coding these values in BizTalk is not possible. The Database Lookup table can be used to access any ODBC compliant data source.

Getting started with the Database Lookup Functoid

In order to illustrate the use of the Database Lookup Functoid; let's suppose that our application receives a simple purchase order with the following schema (see Figure 1) and it's required to generate an invoice (see Figure 2) from the purchase order using mapping techniques in BizTalk Server.

Figure 1

Figure 2

In order to correctly map the Invoice document from the Purchase Order document, we need to look up the name and price of the product in the AdventureWorks database that shipped with SQL Server 2005 using the Database Lookup functoid which can retrieve a recordset.

First of all add a map artifact to your BizTalk solution (see Figure 3).

Figure 3

Then open the Purchase Order schema in the Source Schema pane of the map, and the Invoice schema in the Destination Schema pane of the map (see Figure 4).

Figure 4

Now drag and drop a Database Lookup Functoid from Database Functoids tab in the Toolbox onto the map surface.

The Database Lookup Functoid requires four parameters. The first parameter is used in the search criteria. To configure this parameter, drag and drop the ProductID node from the Purchase Order schema onto the left side of the functoid. The second parameter is a full connection string for the database with a provider, machine name, database and authentication. The third parameter is the nme of the table used in the search. And finally, the fourth parameter is the name of the column used in the search (see Figure 5).

Figure 5

Now drag and drop two Value Extractor functoids from Database Functoids tab in the Toolbox onto the map surface for each extracted value from the return recordset, in this case, the Name and ListPrice column from the Production.Product table. Then for each Value Extractor functoid, connect the left side of the Value Extractor to the right side of Database Lookup. Finally for each Value Extractor functoid, create a constant with the name of the extracted column (see Figure 6 and Figure 7).

Figure 6

Figure 7

In order to calculate the Total in the Invoice document, we need to drag and drop an Addition functoid from Mathematical Functoids tab in the Toolbox onto the map surface. Now connect the Quantity node in the Purchase Order document and the Value Extractor functoid with ListPrice value to the Addition functoid.

Finally for each Value Extractor functoid, connect the right side of the functoids to the appropriate target schema outbound node.

The final map is depicted as shown in Figure 8.

Figure 8

Now, let's test the map with the following purchase order document (see Listing 1).

<ns0:PurchaseOrder xmlns:ns0="http://BizTalkMessagingEx.PurchaseOrder_Schema">
  <ProductID>1</ProductID>
  <Quantity>4</Quantity>
</ns0:PurchaseOrder>

Listing 1

Conclusion

In this article, I covered through an example how to use the Database Lookup functoid in BizTalk maps.