Mapping your data involves:
Importing the definition of the source data – the BizTalk XML schema.
Importing the definition of the destination data – the table definition of the ODBC database used by your proprietary inventory and billing application.
Mapping the XML schema to the ODBC database table format.
Creating the map flow.
A sample of the XML purchase order data that you receive is shown below. You import the record definition using the BizTalk schema associated with this data. For your reference, a copy of the schema is included at the end of this document in “XML PO SCHEMA (BizTalk 0.8)”.
<?xml version="1.0" ?> <BizTalk xmlns="urn:schemas-biztalk-org:biztalk/biztalk-0.81.xml"> <Route> <From locationID="http://www.MPT.com" locationType="HTTP" process="http://MPT.com/biztalk" path="" handle="45" /> <To locationID="http://www.ToysForBusinesses.com/biztalk/recv.asp" locationType="HTTP" process="" path="http://ToysForBusinesses.com/biztalk/recv.asp" handle="45" /> </Route> <Body> <PO xmlns="urn:schemas-biztalk.org:fabrikam/orders.xml"> <POHeader refPromise="0" fromCust="Major Playtime Tools" poNumber="12345" description="Sample PO" paymentType="INVOICE" shipType="AIR1D" /> <Contact contactName="Billy Badger" contactPhone="(425) 123-1234" contactEmail="bbadger@MPT.com" /> <POShipTo street1="8230 Old Courthouse Road" street2="Suite 100" street3="" street4="" attn="Susie Sandella" city="Vienna" stateProvince="VA" postalCode="22182" country="USA" /> <POBillTo street1="8230 Old Courthouse Road" street2="Suite 300" street3="" street4="" attn="Billy Badger" city="Vienna" stateProvince="VA" postalCode="22182" country="USA" /> <POLines count="2" startAt="1"> <Item line="1" partNo="VoiceActivatedKeyboard" qty="1" unitPrice="3000" uom="Unit" discount="0.0" needAfter="2000-07-31" needBefore="2000-05-29" /> <Item line="2" partNo="VirtualVacationPackage" qty="5" unitPrice="4500" uom="Unit" discount="0" needAfter="2000-07-31" needBefore="2000-05-29" /> </POLines> </PO> </Body> </BizTalk>
Defining the source data – importing
a record definition from BizTalk XML schema
In this example, you import the record definition for the source data from a BizTalk XML schema.
From the Application Files menu at the top of the main ECMap window, select Import Definition.
Select XML from the Import Definition submenu, and then select Schema from the XML submenu.
The XML Schema Import window displays. It has three tabs - Schema Import, XML Elements, and XML Attributes.
On the Schema Import tab, select the button on the far right of the window at the end of the File to load text box.
The Open window displays. At the bottom of the window, choose Any files from the Files of type drop-down list.
On the Open window, locate c:\..\data\poschema.xsd and double-click it.
You return to the XML Schema Import window, and the program automatically populates the File to load text box with the full-path file name - C:\..\data\poschema.xsd.
Select Create Records and the program automatically creates record definitions from the XML schema.
The unexpanded XML schema tree displays on the left side of the Schema Import tab - three lines display initially, with one unexpanded XML element called Schema. The associated SAX events display on the right side of the tab.
The globe symbol indicates an XML element, and the cloud symbol indicates an XML attribute. If you navigate down into the XML schema tree, you reveal the elements and attributes. You can also view elements on the XML Elements tab and attributes on the XML Attributes tab.
Close the XML Schema Import window to return to the main ECMap window.
Designating XML Records as Source
On the ECMap window, select Records/Tables on the toolbar at the top of the main ECMap window.
The Records/Tables window displays. (Initially, only the elements display. However, if you navigate down through the XML element “records”, the XML attribute “fields” display.)
XML elements are indicated by Records/Tables and are equivalent to ECMap “records”. XML attributes are indicated by Records/Tables and are equivalent to ECMap “fields”.
Right-click on the BizTalk XML “record” element (listed in bold below) and choose Record Properties from the drop-down menu.
The Record Properties window displays. It has two tabs - Required and Optional. For each of the XML elements listed below, select the Optional tab to make it active and then choose Source as the Record Type from the drop-down list. Select Next. Do this for each of the three tables. Select OK to return to the Records/Tables window.
If you select the Record Type text box and type S, and
the program automatically enters Source for you in the Record Type
text box.
When you have designated all of the elements as Source, close the Records/Tables window to return to the main ECMap window.
The record definitions extracted from the XML schema are shown below. Bold XML elements are equivalent to records. XML attributes nested beneath the elements are equivalent to fields.
BizTalk
BizTalk_data
xmlns
Body
Body_data
Contact
Contact_data
contactPhone
contactEmail
contactName
From
From_data
LocationType
locationID
handle
process
path
Item
Item_data
Qty
NeedBefore
discount
unitPrice
needAfter
line
uom
partNo
PO
PO_data
xmlns
POBillTo
POBillTo_data
Street4
Street3
Street2
Street1
stateProvince
city
country
attn
postalCode
POHeader
POHeader_data
RefPromise
description
fromCust
paymentType
shipType
poNumber
POLines
POLines_data
count
startAt
POShipTo
POShipTo_data
Street4
Street3
Street2
Street1
stateProvince
city
country
attn
postalCode
Route
Route_data
To
To_data
LocationType
locationID
handle
process
path
Creating the path to the input XML data file
You have defined the XML data to be used as input to the map so that ECMap knows how to read it. Now you must tell ECMap where to find the XML data. You do this by defining the path to the file in which the XML input data is stored.
Select Application Directories on the main ECMap window and the Directories (Mailboxes) window displays.
Choose New from the File menu on the Directories (Mailboxes) window, and the New Directory window displays.
Instead of choosing New from the File menu, you can
select New in the row of icons beneath the menu items.
Select Browse and the Select a Directory... window displays.
Navigate down to c:\..\data and select OK. You return to the New Directory window and ECMap enters c:\..\data into the Directory text box.
Select c:\..\data on the Directories(Mailboxes) window to highlight the directory, and choose Open from the Application Files menu at the top of the Directories(Mailboxes) window.
The Files/ Databases window displays to the right of the Directories (Mailboxes) window. Choose New from the File menu on the Files/Databases window, and the New File window displays.
The directory appears to be highlighted and active when you return to the window, but it is not. Four icons appear under the menu options at the top of the window when a directory is active.
On the New File window, select Browse to search for the File Name. The Choose Application File window displays. In the path c:\..\data, locate the file podata.xml and double-click it.
You return to the New File window and ECMap automatically enters podata.xml as the File Name. For File Type, choose HTML/XML from the drop-down list. File Description and Decimal Indicator are optional. Select OK to return to the Files/Databases window.
If you start typing, ECMap enters the File Type for you automatically.
Select podata.xml on the Files/Databases window to highlight the file, and choose Open from the Record menu. The Records/Tables window displays to the right of the Files/Databases window.
The file appears to be highlighted and active when you return to the window, but it is not. Four icons appear under the menu options at the top of the window when a file is active.
Linking the directory, file, and records
Drag podata.xml from the Files/Databases window and drop it on c:\..\data on the Directories (Mailboxes) window. It displays beneath the folder C:\..\data.
Select the Records/Tables window to make it active. From the View menu on the Records/Tables window, choose Record List. The Record Listing window displays. On this window, left-click the first record, press the Shift key, and left-click the last record - continuing to keep the left mouse button depressed. Without lifting your finger from the left mouse, drag all of the highlighted records (which display as one file being dragged) and drop them onto the file podata.xml on the Files/Databases window. All of the records now display beneath the file podata.xml.
Make sure that the element displays under the file after
you have dropped it.
By linking the records to the file, and the file to the directory, you have created the path for the input XML data file so that ECMap knows where to find the XML data that is the input to the map – c:\..\data\podata.xml. Close the four open windows – Record Listing, Records/Tables, Files/Databases, and Directories (Mailboxes).
Defining the destination data – importing
ODBC database table definitions
Next, you import the three table definitions (listed below) from the PO_DATA ODBC database used by your proprietary Inventory and Billing application.
PO_Information PoNumber PO_xmls refPromise fromCust description paymentType shipType contactName contactPhone contactEmail Shipto_street1 Shipto_street2 Shipto_street3 Shipto_street4 Shipto_attn Shipto_city Shipto_stateProvince Shipto_postalCode Shipto_country Billto_street1 Billto_street2 Billto_street3 Billto_street4 Billto_attn Billto_cityBillto_stateProvince Billto_postalCode Billto_country PO_LineItems poNumber partNo qty unitPrice uom discount needAfter needBefore PO_Routing poNumber To_From locationID locationType process path handle
Importing the record definition
From the Application Files menu at the top of the main ECMap window, select Import Definition.
Then select ODBC from the Import Definition submenu. The ODBC Record Definition Import window displays. ECMap automatically populates the Project and Map names.
Select the up arrow next to the Data Source Name in the center section of the ODBC Record Definition Import window. The Data Source Names window displays, with a list of all current DSNs.
Double-click BILLING (the DSN you set up earlier that points to the PO_DATA database) on the Data Source Names window.
You return to the ODBC Record Definition Import window, and the program populates the Data Source Name text box with BILLING and the Driver Connect String text box with DSN=BILLING. Select Continue at the bottom of the window. The Table Selection window displays.
All of the database tables associated with this map display in the bottom section of the window (together with a number of system tables that all start with “MS”). Scroll down through the tables and select the check boxes of the following tables in your PO_DATA database.
PO_Information
PO_LineItems
PO_Routing
In ECMap, tables are the equivalent of records. Select OK at the bottom of the window, and the program extracts the table (“record”) definitions.
A Complete dialog box displays with the message “Record Definitions Imported”. Select OK to close the dialog box.
Designating the ODBC records as destination
Select Records/Tables on the toolbar at the top of the main ECMap window, and the Records/Tables window displays. The ODBC tables (“records”) you imported display on the window. They are included alphabetically in the list of XML schema elements (“records”) that you imported earlier.
Right-click on one of the tables listed in Step 4 and choose Record Properties from the drop-down menu. The Records/ Tables window displays. It has two tabs - Required and Optional. Select the Optional tab and choose Destination as the Record Type. Select Next. Do this for each of the three tables. Select OK to return to the Records/Tables window.
If you select the Record Type text box and type D, and the program enters Destination for you in the Record Type text box automatically.
Close the Records/Tables window to return to the main ECMap window.
Creating the connection to the inventory and billing database
You have defined the ODBC tables to which the map writes the output data. Now you must tell ECMap where to find the database that contains these tables. You do this by linking the three tables with the database.
Select Files/Databases on the main ECMap window, and the Files/Databases window displays. Choose New from the File menu, and the New File window displays.
On the New File window, select Browse to search for the File Name. The Choose Application File window displays. In the path c:\..\data, locate the database PO_DATA.mdb and double-click it.
You return to the New File window and PO_DATA.mdb is entered as the File Name. For the File Type, choose SQL DATABASE from the drop-down list. The appearance of the window changes.
Select the up arrow next to the Data Source Name text box, and the Data Source Names window displays.
Double-click BILLING and you return to the New File window.
The program populates the Data Source Name text box with BILLING and constructs the driver connect string, DSN=BILLING, which it uses to populate the Driver Connect String (May Be Altered) text box. Select OK to close the New File window. PO_DATA.mdb now displays as one of the files.
To make sure that the DSN is configured properly, select the Test SQL Connection button. The message Connection Established indicates that the setup is correct.
Select PO_DATA.mdb on the Files/Databases window to highlight it.
Choose Open from the Record menu, and the Records/Tables window displays to the right of the Files/Databases window.
The file appears to be highlighted and active when you return to the Files/Databases window, but it is not. You can tell when a file is active because Four icons appear under the menu options at the top of the window.
The records associated with this map display on the Records/ Tables window, including the three tables in the Inventory and Billing database.
PO_Information
PO_LineItems
PO_Routing
One at a time, drag each of the three tables listed above from the Records/Tables window and drop it on PO_DATA.mdb on the Files/Databases window. The tables should now display beneath database PO_DATA.mdb.
Make sure that each element displays under PO_DATA.mdb after you have dropped it.
You have now linked the tables to the Inventory and Billing database. Close the Files/Databases window and the Records/Tables window.
Creating the map flow
The map flow defines the relationship between the source data and the destination data. It is driven by rules and levels that you create and assign. In this example, XML elements are mapped to ODBC records. You have already defined the source and destination data and told the program where the input and output data are located. Now you set up levels and rules, and use them to create flow.
Select Map Flow on the toolbar at the top of the window. The Any-to-Any Map Flow window displays. From the Option menu at the top of the window, make sure that Multiple Files is unchecked. (If it is checked, select it and ECMap unchecks it. You can view it again to make sure that it is unchecked.)
To toggle between checking and unchecking an option,
simply select on the option.
Planning and creating levels:
Create levels based on the structure of the XML data you are reading and the ODBC output you are writing. In this example, you are reading the BizTalk XML schema included at the end of this document.
100 BizTalk Level 200 Route Level 300 From Level 310 To Level 400 Body Level 500 PO Level 600 PO Header Level 610 Contact Level 620 Ship To Level 630 Bill To Level 800 PO Lines Level 900 Item Level
On the Levels window, create the levels that are used in defining map flow. Assign a number to each level. While these numbers are arbitrary, they generally reflect the hierarchy of the data.
Always have a master level, and it must always be the first level.
Choose Levels from the View menu at the top of the Any-to-Any Map Flow window.
The Levels window displays.
On the Levels window, choose New from the File menu, and the New Level window displays.
Create a new level for each of the levels listed above, by entering the level number in the first text box and the level description in the second text box.
As you define a new level, it displays on the Levels window. (If you move the New Level window so that it is not directly over the Levels window, you can see the levels display on the Levels window as you create them.) Begin by entering 100 in the first text box of the New Level window and BizTalk Level in the second text box. Select Next and add the next level - 200 Route Level. When you have finished adding all of the levels, select OK to close the New Level window. Close the Levels window to return to the Any-to-Any Map Flow window.
Select Tab to move from the first text box to the second
text box. When you finish defining one level and want to define
another, select Next on the window or press Alt-N.
Planning and creating rules
On the Any-to-Any Map Flow window, choose Rules from the View menu. The Rule Definitions window displays. On this window, you create the rules that are used in defining map flow. In the current mapping example, you read each XML element in a separate rule command. As you read elements that contain data to be mapped, you perform the mapping in additional “assignment” rule commands. Finally, you write the output in other rule commands. Rule numbers are arbitrary and generally reflect the sequence of events. You create the rules based on the elements that must be read, the data that must be mapped, and the output that must be created, as shown below:
Do Nothing 100 Read <BizTalk> 200 Read <Route> 300 Read <From> 305 Map <From> 308 Write PO_Routing 310 Read <To> 315 Map <To> 318 Write PO_Routing 400 Read <Body> 500 Read <PO> 505 Map <PO> 600 Read <POHeader> 605 Map <POHeader> 610 Read <Contact> 615 Map <Contact> 620 Read <POShipTo> 625 Map <POShipTo> 630 Read <POBillTo> 635 Map <POBillTo> 700 Write <PO_Information> 800 Read <POLines> 900 Read<Item> 905 Map <Item> 1000 Write <POLines>
For each rule command shown above, do the following:
At this point, you are simply creating the rules. Later, you add the actual rule commands that do the reading, writing, and mapping.
On the Rule Definitions window, select New at the top of the window or choose New Rule from the File menu. The Rule Definition - New window displays.
On the Rule Definition - New window, enter the Rule Number and the Rule Description. (The Do Nothing already exists.)
Select OK on the window or press Enter.
As you enter a rule, it displays on the Rule Definitions window. Begin by entering 100 in the Rule Number text box and Read <BizTalk> in the Rule Description text box. Continue adding rules until you have finished with 1000 Write <POLines>. When all of the rules have been entered, close the Rule Definitions window.
Creating and defining the map flow table
Now that you have planned and set up levels and rules, you are ready to create map flow, by associating levels, records, and rules with each flow point. Choose New from the File menu on the Any-to-Any Map Flow window, and then choose Add Master Level from the New submenu. The Any-to-Any Record Flow window displays next to the Any-to-Any Map Flow window. It has two tabs – Required and Advanced. The Required tab is active initially and has four sections – Levels, Record Type, Records, and Rules. Beginning with the information for level 100 (shown beginning on the next page) and ending with level 1000, follow the three steps outlined below to enter the flow information for each flow point on the Required tab of the Any-to-Any Record Flow window. (Enter only information shown in bold. ECMap automatically enters the other information on the Required tab for you. The program also automatically enters correct information on the Advanced tab, but not until after you have added the following level.)
Under Levels, select the up arrow next to the Current text box, and the Levels window displays. Double-click a level and the program enters it automatically in the Current level text box.
Under Records, select the up arrow next to the Current text box, and the Records/ Tables window displays. Double-click a record and the program enters it automatically in the Current record text box.
Under Record Type, the program has already checked the correct type - based on the attributes in the schema that you imported. The only instance where you change this is for repeating elements/records. While it is Mandatory to have a detail record, it is optional to have multiple records. In this case, you must change Mandatory to Optional (No Msg) and check Repeating.
Under Rules, select the up arrow next to the I/O text box, and the Rule Definitions window displays. Double-click a rule and the program enters it automatically in the I/O rule text box. Double-click another rule and the program enters it automatically in the Before rule text box. Double-click another rule and the program enters it automatically in the After rule text box. (If you need only an I/O rule or only I/O and Before rules, select OK to close the Rule Definitions window after you have added the last rule, rather than double-clicking to add another rule.)
After all the information for a flow point has been entered, select OK to add another flow point.
At the bottom of the Any-to-Any Map Flow window, the following information displays for the currently highlighted flow point:
Current Level
I/O Rule
Before Rule
After Rule
Next Level
Break Level
Associating levels, records, and rules with flow points:
The Any-to-Any Record Flow window is open, ready for you to add the master level.
If the Any-to-Any Record Flow window
is not open, choose New from the File menu on the Any-to-Any
Map Flow window, and then choose Add Master Level from the New submenu.
The Any-to-Any Record Flow window displays.
Following the four steps in the instructions under Creating and Defining the Map Flow Table, enter on the Required tab of the Any-to-Any Record Flow window the information shown below in bold.
Current Level 100 Parent Level N/A Record Type N/A Current Record BizTalk Parent Record N/A I/O Rule 100 Before Rule 0 After Rule 0
Select OK to create this flow point and close the Any-to-Any Record Flow window. After you create the next flow point, the program enters the following information on the Advanced tab for this flow point:
Next Level 200 Break Level 100
Right-click level 100 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Child Level from the New submenu.
When the XML schema is imported, the program stores information
about the element, such as whether it is mandatory or optional.
On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
Current Level 200 Parent Level 100 Record Type Mandatory Current Record Route Parent Record BizTalk I/O Rule 200 Before Rule 0 After Rule 0
Select OK to create this flow point and close the Any-to-Any Record Flow window. After you create the next flow point, the program enters the following information on the Advanced tab for this flow point:
Next Level 300 Break Level 100
Right-click level 200 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Child Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
Current Level 300 Parent Level 200 Record Type Mandatory Current Record From Parent Record Route I/O Rule 300 Before Rule 305 After Rule 308
Select OK to create this flow point and close the Any-to-Any Record Flow window. After you create the next flow point, the program enters the following information on the Advanced tab for this flow point:
Next Level 310 Break Level 100
Right-click level 300 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Sibling Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
Current Level 310 Parent Level 200 Record Type Mandatory Current Record To Parent Record Route I/O Rule 310 Before Rule 315 After Rule 318
Select OK to create this flow point and close the Any-to-Any Record Flow window. After you create the next flow point, the program enters the following information on the Advanced tab for this flow point:
Next Level 400 Break Level 100
Right-click level 100 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Child Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
You must right-click level 100. You are creating
a new child level of the master level.
Current Level 400 Parent Level 100 Record Type Mandatory Current Record Body Parent Record BizTalk I/O Rule 400 Before Rule 0 After Rule 0
Select OK to create this flow point and close the Any-to-Any Record Flow window. After you create the next flow point, the program enters the following information on the Advanced tab for this flow point:
Next Level 500 Break Level 100
Right-click level 400 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Child Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
Current Level 500 Parent Level 400 Record Type Mandatory Current Record PO Parent Record Body I/O Rule 500 Before Rule 505 After Rule 0
Select OK to create this flow point and close the Any-to-Any Record Flow window. After you create the next flow point, the program enters the following information on the Advanced tab for this flow point:
Next Level 600 Break Level 100
Right-click level 500 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Child Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
Current Level 600 Parent Level 500 Record Type Mandatory Current Record POHeader Parent Record PO I/O Rule 600 Before Rule 605 After Rule 0
Select OK to create the flow point and close the Any-to-Any Record Flow window. At this point, the program automatically enters the following information on the Advanced tab:
Next Level 610 Break Level 100
Right-click level 600 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Sibling Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
Current Level 610 Parent Level 500 Record Types Mandatory Current Record Contact Parent Record PO I/O Rule 610 Before Rule 615 After Rule 0
Select OK to create the flow point and close the Any-to-Any Record Flow window. At this point, the program automatically enters the following information on the Advanced tab:
Next Level 620 Break Level 100
Right-click level 610 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Sibling Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
Current Level 620 Parent Level 500 Record Type Mandatory Current Record POShipTo Parent Record PO I/O Rule 620 Before Rule 625 After Rule 0
Select OK to create the flow point and close the Any-to-Any Record Flow window. At this point, the program automatically enters the following information on the Advanced tab:
Next Level 630 Break Level 100
Right-click level 620 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Sibling Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
Current Level 630 Parent Level 500 Record Type Mandatory Current Record POBillTo Parent Record PO I/O Rule 630 Before Rule 635 After Rule 700
Select OK to create the flow point and close the Any-to-Any Record Flow window. At this point, the program automatically enters the following information on the Advanced tab:
Next Level 800 Break Level 100
Right-click level 630 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Sibling Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
Current Level 800 Parent Level 500 Record Type Mandatory Current Record POLines Parent Record PO I/O Rule 800 Before Rule 0 After Rule 0
Select OK to create the flow point and close the Any-to-Any Record Flow window. At this point, the program automatically enters the following information on the Advanced tab:
Next Level 900 Break Level 100
Right-click level 800 on the Any-to-Any Map Flow window. Choose New from the drop-down menu and Add Child Level from the New submenu. On the Required tab of the Any-to-Any Record Flow window, enter the information shown below in bold:
For level 900, be sure to change the Record Type from Mandatory
to Optional and to check the Repeating check box. This is the only
level for which you change the Record Type. Right-click level
620 on the Any-to-Any Map Flow window. Choose
New from the drop-down menu and Add Sibling Level from
the New submenu. On the Required tab of the Any-to-Any
Record Flow window, enter the information shown below in bold:
Current Level 900 Parent Level 800 Record Type Optional (No Msg) Repeating Current Record Item Parent Record POLines I/O Rule 900 Before Rule 905 After Rule 1000
Select OK to create the flow point and close the Any-to-Any Record Flow window. At this point, the program automatically enters the following information on the Advanced tab:
Next Level 900 Break Level 100
When you have finished building the map flow, the Any-to-Any Map Flow window should look like the representation shown below:
For each flow point, or entry in the flow table, the level number and description display on the main window. As you select each flow point, the associated levels and rules display in a separate section at the bottom of the window.
Close the Any-to-Any Map Flow window and any other windows that remain open, such as Levels, Records/Tables, or Rule Definitions.
Reading the XML data and writing to the ODBC database
The last step that you perform before doing the actual mapping is to enter the rule commands to read the XML data and write the ODBC data. For this tutorial, the XML source data is located in c:\..\data\podata.xml, and the destination data is stored in PO_DATA.mdb (in the Inventory and Billing database pointed to by the DSN BILLING).
Select Rules on the toolbar at the top of the window, and the Rule Definitions window displays. The rules that you added earlier display on the window.
Adding the commands to read the XML data
Right-click rule 100 and choose New Command from the drop-down menu. The Rule Command - New window displays. Select XML I/O from the drop-down list for the Command text box and additional text boxes appear at the bottom of the window.
If you begin typing X.. in the Command text box, ECMap automatically
enters it for you.
Apply is used to save a command and enter a new command. OK is used to save a command and exit the New Command window.
Select the I/O Command up arrow and choose Read XML from the drop-down menu.
Select the Application Directory up arrow and the Directories (Mailboxes) window displays. Navigate down through c:\..\data\podata.xml and double-click BizTalk.
The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK on the Rule Command-New window to add the command and close both the Directories(Mailboxes) and the Rule Command-New windows. The new command displays under rule 100.
Right-click rule 200 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu.
Enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click Route. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close the Directories(Mailboxes) window and the Rule Command-New window.
Right-click rule 300 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu.
Enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click From. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories(Mailboxes) and the Rule Command-New windows.
Right-click rule 310 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu.
Enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click To. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories (Mailboxes) and the Rule Command-New windows.
Right-click rule 400 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu.
Enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click Body. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories (Mailboxes) and the Rule Command-New windows.
Right-click rule 500 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu.
Enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click PO. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories (Mailboxes) and the Rule Command-New windows.
Right-click rule 600 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu.
Enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click POHeader. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories(Mailboxes) and the Rule Command-New windows.
Right-click rule 610 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu.
Enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click Contact. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories (Mailboxes) and the Rule Command-New windows.
Right-click rule 620 and repeat the actions you performed in Step 3.
Choose New Command from the drop-down menu, and then enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click POShipTo. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories (Mailboxes) and the Rule Command-New windows.
Right-click rule 630 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu, and then enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click POBillTo. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories (Mailboxes) and the Rule Command-New windows.
Right-click rule 800 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu, and then enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click POLines. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories (Mailboxes) and the Rule Command-New windows.
Right-click rule 900 and repeat the actions you performed in Step 2.
Choose New Command from the drop-down menu, and then enter XML I/O as the Command and Read XML as the I/O Command on the Rule Command–New window.
Navigate down into c:\..\data\podata.xml and double-click Item. The program automatically populates the Application Directory, Application File, and File Record text boxes.
Select OK to close both the Directories(Mailboxes) and the Rule Command-New windows.
Adding the commands to write the ODBC data
Right-click rule 308.
Choose New Command from the drop-down menu, and then enter SQL as the Command and Insert Record into Table as the I/O Command on the Rule Command – New window.
Select the up arrow next to SQL Application File Name, and the Files/Databases window displays.
Double-click on PO_DATA.mdb.
Double-click on PO_Routing. The program automatically populates the SQL Application File Name, SQL Application File Record, and SQL Table Name text boxes.
Select OK on Rule Command – New window.
Right-click rule 318.
Choose New Command from the drop-down menu, and then enter SQL as the Command and Insert Record into Table as the I/O Command on the Rule Command – New window.
Select the up arrow next to SQL Application File Name, and the Files/ Databases window displays.
Double-click on PO_DATA.mdb.
Double-click on PO_Routing. The program automatically populates the SQL Application File Name, SQL Application File Record, and SQL Table Name text boxes.
Select OK on Rule Command – New window.
Right-click on Rule 700 and choose New Command.
On the Rule Command – New window, enter SQL as the Command and Insert Record into Table as the I/O Command.
On the Files/Databases window, double-click PO_Information. The program automatically populates the SQL Application File Name, SQL Application File Record, and SQL Table Name text boxes on the Rule Command – New window.
Select OK on that window to add another command.
Right-click rule 1000 and choose New Command from the drop-down menu.
Enter SQL as the Command and Insert Record into Table as the I/O Command on the Rule Command – New window.
Select the up arrow next to SQL Application File Name, and the Files/ Databases window displays.
Navigate down through c:\..\data\ PO_DATA.mdb and double-click PO_LineItems. The program automatically populates the SQL Application File Name, SQL Application File Record, and SQL Table Name text boxes.
Select Apply.
Rule 1000 has two commands. When you finish adding the
first rule command, select Apply instead of OK. This adds the rule
command, but does not close the window. After you add the second
rule command, select OK.
Enter the second command. The Rule Command – New window should still be open.
Enter Clear Record as the Command, and the window changes.
Select the up arrow next to Record to Clear, and the Records/ Tables window displays.
Double-click PO_LineItems, and the program automatically enters it in the Record to Clear text box.
Select the up arrow next to Type of Record Clear, and choose Clear All Fields from the submenu.
Select OK to close the Rule Command – New and Records/Tables windows.
Do not close the Rule Definitions window.
Mapping the source data to the destination data
Mapping in ECMap is a simple procedure of linking source record fields or elements to destination record fields or elements. Source data is on the left side of the Any-to-Any Map window, destination data is on the right, and the results of mapping display at the bottom of the window. In addition, there is a temporary mapping workspace in the middle of the window, where you can map multiple records at one time.
You can create mapping links in one of two ways:
Drag source objects and drop them on destination objects one at a time.
Enter multiple source and destination objects in the temporary mapping workspace and map them simultaneously with the select of a button.
Using the Mapping icon at the top of the Rule Definitions window and the View Rules button at the bottom of the Any-to-Any Map window, toggle back and forth between the two windows.
The Rule Definitions window should be open. Right-click rule 305, choose New Command from the drop-down menu, and enter Assignment as the Command. As the Source under Parameters, choose Constant from the drop-down list and the Constant Value dialog box displays.
Enter From as the constant value and select OK.
As the Destination under Parameters, choose Record/Field and the Records/Tables window displays.
Under PO_Routing, double-click To_From. Select OK to close the Rule Command-New and Records/Tables windows.
The Line number specifies the order of the rule command in the rule.
Select Mapping from the icons at the top of the Rule Definitions window and the Any-to-Any Map window displays. Under Rule in the upper right corner of the window, 305 displays in the rule Number text box and 1 displays in the Line text box. Type 2 in the Line text box.
On the Source side of the Any-to-Any mapping window, double-click From.
Under Destination, double-click PO_Routing.
Drag the following elements under From and drop them on the specified fields under PO_Routing:
On the Any-to-Any Map window, the
XML elements display under Source, and the ODBC database tables
display under Destination.
Source Destination From PO_Routing location ID locationID locationType locationType process process path path handle handle
The mapping displays in the bottom middle section of the window.
Select View Rules at the bottom of the Any-to-Any Map window, and the Rule Definitions window displays:
Right-click rule 315, choose New Command from the drop-down list, and enter Assignment as the Command.
As the Source under Parameters, choose Constant from the drop-down list and the Constant Value dialog box displays.
Enter To as the constant value and select OK.
At the Destination under Parameters, choose Record/Field and the Records/Tables window displays.
Under PO_Routing, double-click To_From.
Select OK to close the Rule Command-New and Records/Tables windows.
Select Mapping on the Rule Definitions window and the Any-to-Any Map window displays.
Under Rule in the upper right corner of the window, 315 displays in the rule Number text box and 1 displays in the Line text box.
Type 2 in the Line text box.
Under Source on the Any-to-Any mapping window, double-click To.
Under Destination, the elements under PO_Routing are already displayed.
Drag the following elements under To and drop them on the specified fields under PO_Routing:
Source Destination To PO_Routing location ID locationID locationType locationType process process path path handle handle
Select the up arrow next to the Number text box in the top right Rule section of the window, and the Rule Definitions window displays.
Double-click rule 505, and the program automatically enters 505 in the Number text box and 1 in the Line text box.
Minimize the Rule Definitions window.
Under Source, double-click PO.
Under Destination, double-click PO_Information.
Drag xmlns under PO and drop it on PO_xmlns under PO_Information.
Select the up arrow next to the Number text box in the top right Rule section of the window, and the Rule Definitions window displays.
Double-click rule 605 and the program enters 605 in the Number text box and 1 in the Line text box.
Select Mapping button and clear middle part of window.
Minimize the Rule Definitions window.
Under Source, double-click POHeader
Double-click each element in the order listed below.
Under Destination, double-click PO_Information and double-click all of the same elements in the order listed below:
Source Destination POHeader PO_Information refPromise refPromise fromCust fromCust poNumber PONumber description description paymentType paymentType shipType shipType Select => (Apply) to map elements.
As you select elements, they display in the mapping area in the top middle section of the window. When you are finished, select the minus button and each element is mapped to the element directly across from it. The results display at the bottom of the window, and the mapping area at the top is cleared.
Select the up arrow next to the Number text box in the top right Rule section of the window, and the Rule Definitions window displays.
Double-click rule 615 and the program enters 615 in the Number text box and 1 in the Line text box.
Minimize the Rule Definitions window.
Select View Mapping and clear the middle part of the window.
Under Source, double-click Contact and then double-click each element in the order listed below.
On the Destination side of the window, under PO_Information, double-click all of the same elements in the order listed below:
Source Destination Contact PO_Information contactName contactName contactPhone contactPhone contactEmail contactEmail
Select the minus between the two sets of data and the mapping is performed.
Select the up arrow next to the Number text box in the top right Rule section of the window, and the Rule Definitions window displays.
Double-click rule 625 and the program enters 625 in the Number text box and 1 in the Line text box.
Minimize the Rule Definitions window.
Select View Mapping and clear the middle part of the window.
Under Source, double-click POShipTo and then double-click each element in the order listed below.
On the Destination side of the window, under PO_Information, double-click the corresponding elements in the order listed below:
Source Destination POShipTo PO_Information street1 Shipto_street1 street2 Shipto_street street3 Shipto_street3 street4 Shipto_street4 attn Shipto_attn city Shipto_city stateProvince Shipto_stateProvince postalCode Shipto_postalCode country Shipto_country
Select the minus between the two sets of data and the mapping is performed.
Select the up arrow next to the Number text box in the top right Rule section of the window, and the Rule Definitions window displays.
Double-click rule 635 and the program enters 635 in the Number text box and 1 in the Line text box.
Minimize the Rule Definitions window.
Select View Mapping and clear the middle part of the window.
Under Source, double-click POBillTo and then double-click each element in the order listed below.
On the Destination side of the window, under PO_Information, double-click the corresponding elements in the order listed below:
Source Destination POBillTo PO_Information street1 Billto_street1 street2 Billto_street2 street3 Billto_street3 street4 Billto_street4 attn Billto_attn city Billto_city stateProvince Billto_stateProvince postalCode Billto_postalCode country Billto_country
Select the minus between the two sets of data and mapping is performed.
Select the up arrow next to the Number text box in the top right Rule section of the window, and the Rule Definitions window displays.
Double-click rule 905 and the program automatically enters 905 in the Number text box and 1 in the Line text box.
Minimize the Rule Definitions window.
Select View Mapping and clear the middle part of the window.
Under Source, double-click Item and then double-click each element in the order listed below.
Under Destination, close PO_Information, double-click PO_LineItems and double-click all of the same elements in the order listed below:
Source Destination Item PO_LineItems partNo partNo qty qty unitPrice unitPrice uom uom discount discount needAfter needAfter needBefore needBefore
Under Source, double-click POHeader and then double-click poNumber. Under Destination, double-click PONumber.
Select the minus between the two sets of data and the mapping is performed.
Close the Any-to-Any Map window. You have now finished mapping and are now ready to generate and run your map. Close all open windows.
