SQL Adapter, Updategrams and Multiple Tables
When using the SQL Adapter, it provides the ability to auto generate an Updategram schema based on the table that you want to manipulate. To do this, run the wizard and specify the Updategram option and then choose the table name and columns to include in the generated schema. After you do this you will end up with a schema that looks like the following image.
You can then take this schema and use it in the Mapper and manipulate data for one table. This process works very nice if you only want to modify one table per round trip. What happens if you need to modify more than one table?
The problem that we have is that the wizard will only let you choose one table name each time that the wizard runs. The SQL XML libraries will allow you to have as many tables in the schema as you would like.
So, there are two possible solutions to this problem.
The first is to run the wizard for each table that you want to include and then open the first schema, navigate to the <xs:sequence> tag under the after element. Within the sequence tag paste each of the table schema declarations for each of the tables that you need included.
The second is to add the new record to the schema under the after record and hand enter each of the elements that correspond to the fields in the table.
When you are finished you should end up with a schema that looks like the following image.
You can now take this updated schema and use it in the mapper to manipulate data for as many tables in the database that your message can populate.
The thing to remember is that just because BizTalk doesn't do this by default doesn't mean that you can't do it or that BizTalk won't be able to handle it.
In my next blog entry I will talk about populating values for parent and child relationships when there is an autoincrement column involved.