Using SQLXML's at-identity with BizTalk

  • Article

I was looking for a way to send data to SQL Server to populate multiple tables in a parent-child relationship without the need to make additional roundtrips.  What I describe below is the solution that I put together.

 

The first thing that I found was that SQL XML has the ability to do this using the at-identity functionality which is described here .  Once I found this then all that I needed to do was to setup BizTalk to create the schema I needed to provide to SQL XML.  What I did is explained below.

 

The first thing I did was to create the updategram schema which will be imported into the main schema (in this example I called it updategram.xsd)

 

When creating this schema delete the record element under the Schema element.  Then add a child field attribute.  Name the attribute at-identity.  Then click on the Schema element and set the targetnamespace property to urn:schemas-microsoft-com:xml-updategram

 

 

We need to create this schema to import because the editor will not let you add the colon in the name (updg:at-identity) which is what it would need to be named if we were just going to add it directly to the schema.

 

After you have created this schema, create a schema using the SQL Adapters.  This will provide you a schema which will represent the parent table.  At this point you need to modify this schema to add the items for the child table.  See my last Blog entry for more information on this functionality.  Once you have modified the SQL Adapter schema to include both the parent and child table, (in this case it is InsertProductService.xsd), click on the Scheme element then click on the Imports property and import the updategram.xsd schema.  Once the item has been added to the grid, click on the Prefix column and change the value from the default of ns0 to updg and click the ok button.  By doing this, BizTalk will automatically add the colon for us.

 

 

Now we need to add this to our schema.  So, add a child field attribute to the schema and select the Data Type drop down in the properties window.  At the bottom of the drop down will be our new type that we imported; updg:at-identity (Reference).  Once this is selected it will automatically rename the attribute to updg:at-identity which is what we want.  Now that we have our new attribute, make sure that it is located directly under the record element.  We now have a schema that is ready to bring into the Mapper.

 

 

 

Open the Mapper and add your source schema as well as the updategram schema (InsertProductService.xsd). 

 

The first thing that we should do is add the functionality for the autoincrement fields.  Add a Concatenation functoid to the mapping grid.  Drag a line from the this functoid to the updg:at-identity attribute.  Drag a second line from the functoid to the primary key of the child table (in this example it is ProductID).  We do not need to connect anything to the primary key of the parent table since it is this column that will receive the autoincrement value that the at-identity functionality will use to populate the value in the child column.  Now that we have wired up the primary key/foreign key values we can map the remaining parts of our schema

 

 

I have also added 2 additional Concatenation functoids to the map to add the $ sign in front of the columns defined as Money data types.

 

Once the map is finished, create both the receive and send ports.  When creating the send port using the SQL Adapter you will need to configure the connection string as well as the root node element of the response document and the documents' target namespace.

 

Also, remember to setup the correct security for SQL Server.  This includes adding insert, update, etc. permissions to the tables.

 

At this point you have configured a system to pass data to SQL Server using the SQL XML libraries without having to make additional round trips to populate the child tables with the appropriate foreign keys.