Updategram with multiple tables with foreign keys
Since SQL Server 2000, updategrams along with OPENXML have been available to insert, update, or delete data using XML documents. An updategram works against the XML views that are provided by the annotated XSD schema that contains the information required to map to the tables and columns to be modified. Using an updategram to manipulate one table in BizTalk is very straight forward because it integrates right into the Add Generated Items wizard for the SQL Adapter. Manipulating more than one table takes a little more effort because the wizard does not allow the user to select more than one table.
Multiple table insert updategram
There are two options to create an updategram in BizTalk for multiple tables; either manually or using the wizard to generate each individual table insert updategram and then manually merging them together. The next three figures show the process of creating an updategram using the Add Generated Wizard for a single table.
Once you have all of the updategrams created, you can merge them into one updategram schema. Because this is an insert, all of the tables are under the after portion of the updategram. If this was an update, you would have a before record that would be used to match the records that would then be updated by the data in the after portion. The figure below displays the final result.
In order to handle identities, an additional schema is required. In the example below, it is called Updategram. The namespace has to be set to urn:schemas-microsoft-com:xml-updategram. As you can see in the figure below, there are two attributes in this schema.
• at-identity – this is used to hold the identity value for the inserted record where an identity exists on the table.
• returnid – this is used to store the value of a return value from the updategram
In order to take advantage of the identity field mapping, you first need to add a reference to the schema that will hold all of the tables that have to be inserted. The prefix for the Updategram.xsd has to be set to updg. After you have added this reference, add an at-identity attribute to any table that you need to access the identity column.
Next, create a map and add in the source and destination schemas. The figure below contains the mapping between the parent table and the child tables with the identity reference. In order to create a mapping between the primary key of the parent table and the foreign key of the child table, you use a string concatenate functoid and specify a unique value to map between these fields. In this example I used the value HDRID. So for all tables that have to reference the identity column of the inserted header record will use the same value. The figure below has this value mapped to several tables. These are all at the header-child level.
In order to create references to the child records of each detail line, an extra step has to be taken in order to ensure that the correct value gets mapped for the relationship. The reason for this is that each child record of a detail record has to be mapped to only that record. If only DTLID was used, it would all mapped to the same value. You can see an example of this below.
< INVDTL Index=”1” at-identity=”DTLID” >
< INVDTL Index=”2” at-identity=”DTLID” >
< INVDTL Index=”3” at-identity=”DTLID” >
< INVALWDTL DetailIndex=”1” Index=”1” INVDTLID=”DTLID” >
< INVALWDTL DetailIndex=”2” Index=”2” INVDTLID=”DTLID” >
< INVALWDTL DetailIndex=”3” Index=”3” INVDTLID=”DTLID” >
The way around this is to use the string concatenate functoid and add in the iteration functoid which is configured from the detail record. This will reference the at-identity attribute to the correct field in the detail child table.
< INVDTL Index=”1” at-identity=”DTLID1” >
< INVDTL Index=”2” at-identity=”DTLID2” >
< INVDTL Index=”3” at-identity=”DTLID3” >
< INVALWDTL DetailIndex=”1” Index=”1” INVDTLID=”DTLID1” >
< INVALWDTL DetailIndex=”2” Index=”2” INVDTLID=”DTLID2” >
< INVALWDTL DetailIndex=”3” Index=”3” INVDTLID=”DTLID3” >
Now create the rest of your mappings to complete the process and deploy.