The BizTalk Mapper

[Previous] [Next]

In Chapter 6, you wrote an XSLT program to convert one XML document into another. This type of document conversion is a critical part of the BizTalk process if your business partners are using different schemas.

Although I like working directly with XSLT, not everyone does, and not all companies can afford to have an XSLT expert on staff. Using the BizTalk Mapper, you can map the records and fields of two different schemas. BizTalk Server 2000 uses these maps to process and translate data into formats that you can share within your own organization or with your partner organizations.

Exercise: Mapping Two Different Purchase Order Schemas

To explore how the BizTalk Mapper works, let's take the schema we just made and create a map from it to another schema that describes a purchase order.

1. Start the BizTalk Mapper from the Start menu by selecting Programs, Microsoft BizTalk Server 2000, and then clicking BizTalk Mapper.
2. You need to find the two schemas to map. From the File menu, select New. In the Select Source Specification Type dialog box, click Local Files to bring up the Select Source Specification dialog box and find the PurchaseOrder1.xml schema you created in the first example.

The Select Destination Specification Type dialog box should now be visible. Repeat the process for the second schema, but find the purchase order that I created on the companion CD. It is in \Samples\Ch12\PurchaseOrder2.xml. Your screen should look like Figure 12-3.



Figure 12-3. Mapping two different purchase order schemas in BizTalk Mapper.

You create a map by dragging from a field on the left to a matching field on the right. The process is about as simple as that, but this tool adds some other important functionality.

3. From the Tools menu, select Options to bring up the BizTalk Mapper Options dialog box. Select the Allow Record Links check box. Setting this option will allow you to map the textual contents of records rather than just fields. Click OK.
4. Drag and drop the CustomerNumber field in the Source Specification pane onto the customer field in the Destination Specification pane. You will see a line connecting the two, as Figure 12-4 illustrates.



Figure 12-4. You create maps by connecting the fields in the Source Specification pane to fields in the Destination Specification pane.

5. Repeat step 4 three more times, mapping Item with Item, Qty with Quantity, and Price with Price. It's that easy!
6. Notice that the Destination Specification pane has a field that is not included in the Source Specification—Total, the total price for a line item. Without parallel fields, you can't directly map from the Source Specification document (window) to the Destination Specification document. So you'll have to do some computing. This is where the functoid comes in. From the View menu, select Functoid Palette. You'll see the Functoid Palette dialog box, as shown in Figure 12-5.



Figure 12-5. Functoids provide many different computational aids for mapping fields between the source and destination files.

7. The Total field in the Destination Specification is the product of the Qty and Price fields in the Source Specification pane, so you must do a little multiplication. Click on the Mathematical tab.
8. Select the functoid icon that looks like a multiplication symbol. Drag this functoid from the functoid palette into the grid between the Source Specification and the Destination Specification panels. Your screen should resemble Figure 12-6.



Figure 12-6. The multiplication functoid allows you to multiply two numbers in the Source Specification and put the resulting product into a Destination Specification field.

9. Drag a line from the Qty element in the Source Specification to the multiplication functoid.
10. Repeat step 9 with the Price element in the Source Specification.
11. Drag a line from the multiplication functoid to the Total field in the Destination Specification. You have just multiplied two numbers and output the product.
12. You still need to deal with the date. In the Source Specification document, the date field contains a date in a common U.S. form: month/day/year. In the Destination Specification, you must specify the date in ISO 8601 format: YYYY-MM-DD. No functoid exists to convert from one to the other, so you need to create a special script to do that. Click on the Advanced tab in the Functoid Palette dialog box. This tab contains a single functoid—the Custom Visual Basic Script functoid. Drag this functoid into the grid area.
13. Drag a line from the PODate field in the Source Specification to the scripting functoid, and then drag a line from the scripting functoid to the PODate element in the Destination Specification field.
14. Double-click on the scripting functoid, and click on the Script tab. You will see the default script shown in Figure 12-7.



Figure 12-7. The Custom Visual Basic Script functoid allows you to create scripts to assist in transforming a source document into a destination document.

15. Delete the existing function, and replace it with the following script:

 Function mdy2iso8601(dateIn) iFirst = instr(dateIn, "/") iSecond = instr(iFirst + 1, dateIn, "/") iMonth = left(dateIn, iFirst - 1) iDay = mid(dateIn, iFirst + 1, iSecond - iFirst - 1) iYear = mid(dateIn, iSecond + 1, 4) mdy2iso8601 = iYear & "-" & right("0" & iMonth, 2) & _ "-" & right("0" & iDay, 2) End Function 

The mdy2iso8601 function converts a date in the source format (mm/dd/yyyy) into the equivalent that you need. Click OK to close the Functoid Properties dialog box. Your BizTalk Mapper screen should look like the screen in Figure 12-8.



Figure 12-8. The BizTalk Mapper with functoids.

16. Now you need to test the script. Close the Functoid Palette dialog box. Click on the Values tab at the bottom of the BizTalk Mapper window. Make sure the PODate field of the Source Specification pane is highlighted, and then enter a Source test value in the form mm/dd/yyyy, such as 04/27/2000. You'll test this function in a later step. First you'll add more test data for some other fields.
17. Now you need to make sure the multiplication functoid works. Select the Qty field from the Source Specification pane. Enter a value in the field as an integer, such as 95.
18. Select the Price field from the Source Specification pane, and enter a decimal price such as 12.95.
19. Now you are ready to test. From the Tools menu, select Test Map. Notice that the Output tab is brought to the forefront, and the output of the process is displayed. The output should look like this:

 <PurchaseOrder customer="CustomerNumber_1" PODate="2000-04-10"> <LineItem> <Quantity>95</Quantity> <Item>Item_1</Item> <Price>12.95</Price> <Total>1230.25</Total> </LineItem> </PurchaseOrder> 

Notice that the PODate did change from one form to the other, and that the value inside the <Total> tags is the quantity multiplied by the price.

20. Now you need to create an XSLT style sheet that will do the transformation. Open the Tools menu, and then click Compile Map. Notice that the output is an XSLT program. It should look like the output in Listing 12-2.

BizTalk Server 2000 will use the style sheet in Listing 12-2 when a document comes in from a trading partner who sends you XML documents by using the schema defined in PurchaseOrder1.xml.

21. Save the mapping specification by selecting File and then Save Compiled Map As. Name the file PO1-2.xsl. This is an XML document that the BizTalk Server uses to do mapping. You can use Microsoft Internet Explorer to view this file.

Listing 12-2. XSLT style sheet created by BizTalk Mapper.

 PO1-2.xsl <xsl:stylesheet xmlns:xsl='http://www.w3.org/1999/XSL/Transform' xmlns:msxsl='urn:schemas-microsoft-com:xslt' xmlns:var='urn:var' xmlns:user='urn:user' version='1.0'> <xsl:output method='xml' indent='yes' omit-xml-declaration='yes'/> <xsl:template match='/'> <xsl:apply-templates select='PurchaseOrder'/> </xsl:template> <xsl:template match='PurchaseOrder'> <PurchaseOrder> <xsl:attribute name='customer'> <xsl:value-of select='@CustomerNumber'/> </xsl:attribute> <xsl:variable name='var:v1' select='user:mdy2iso8601(string(@PODate))'/> <xsl:attribute name='PODate'> <xsl:value-of select='$var:v1'/> </xsl:attribute> <xsl:for-each select='Item'> <LineItem> <Quantity> <xsl:value-of select='@Qty'/> </Quantity> <Item> <xsl:value-of select='./text()'/> </Item> <Price> <xsl:value-of select='@Price'/> </Price> <xsl:variable name='var:v2' select='user:fctmathmultiply2(string(@Qty), string(@Price))'/> <Total> <xsl:value-of select='$var:v2'/> </Total> </LineItem> </xsl:for-each> </PurchaseOrder> </xsl:template> <msxsl:script language='VBScript' implements-prefix='user'> <![CDATA[ Function FctMathMultiply2( p_strParm0, p_strParm1 ) If ( IsNumeric( p_strParm0 ) And _ IsNumeric( p_strParm1 ) ) Then FctMathMultiply2 = CStr ( CDbl( p_strParm0 ) _ * CDbl( p_strParm1 ) ) Else FctMathMultiply2 = "" End If End Function Function mdy2iso8601(dateIn) iFirst = instr(dateIn, "/") iSecond = instr(iFirst + 1, dateIn, "/") iMonth = left(dateIn, iFirst - 1) iDay = mid(dateIn, iFirst + 1, iSecond - iFirst - 1) iYear = mid(dateIn, iSecond + 1, 4) mdy2iso8601 = iYear & "-" & _ right("0" & iMonth, 2) & _ "-" & right("0" & iDay, 2) End Function ]]> </msxsl:script> </xsl:stylesheet> 

In addition to XML, the BizTalk Mapper can also read and write to other formats, including two widely used EDI specifications: ANSI X12 and EDIFACT. The BizTalk Mapper also allows mapping to Flat files, XML files defined with the document type definition (DTD) schema syntax, and XDR schemas. Even though the BizTalk Mapper can work with these diverse standards, it is optimized for use with the XML-based BizTalk Framework 2.0.