Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Updategram with multiple tables with foreign keys

Shawn

Updategram Overview
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.

You can get to the figure below by right clicking on the project and choosing Add Generated Items.

Next, you need to choose the SQL Adapter that you want to use to connect to the SQL tables and hit Next. Now, you need to add the target namespace for the updategram and specify the port type.

The next step you simply have to select updategram as the type and move on to the next step.

Now you have to select the table and select the columns that you need to insert the data into.

The figure below is the result of the updategram created from the wizard. Repeat these steps for each table that you have to insert/update.

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

I am not covering the returned in this post, but I thought it might be worth a mention as it may be useful for some in the future.

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” >

The figure below displays how to configure the string concatenate functoid and to complete the map for the updategram.

Now create the rest of your mappings to complete the process and deploy.

3 Comments. Leave new

[…] Updategram with multiple tables with foreign keys […]

Shawn,

Do you know how to do the same functionality given in this article using WCF-SQL adapter?

Thanks,
Durga

Hi!

This is just what I am looking for, but the images are gone :-(

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\