I have been struggling with an updategram that will update 3 related SQL tables, and I finally found and adapted the solution at brainjar http://www.brainjar.co.za/blog.aspx.

The basic problem is to update three related tables - tblCustomers, tblProjects and tblTasks say, from within an orchestration using an updategram.Following the brainjar solution, I created a schema called updategram.xsd with a SQLXML recognisable namespace of urn:schemas-microsoft-com:xml-updategram. Then add 2 attributes of type xs:string - at-identity and returnid.I used the SQL adapter wizard to generate an updategram schema for an insert to the first table in my hierarchy - tblCustomers.

I then imported the updategram.xsd schema to the generated schema using Imports from the Schema node. I set the schema prefix to updg in the Imports Collection dialog box.Following the brainjar example, I added a record for tblProjects at the same level as the tblCustomers record and added the relevant fields for tblProjects together with CustomerID.

I also added an attribute of type updg:at-identity (Reference) to the the tblCustomers record.I then set the value of the at-identity attribute of the tblCustomers node to 100 and the the CustomerID value to 100. When the updategram executes these values are replaced by the value of the CustomerID field. How did anyone figure that out?

I then repeated this process in the updategram for the update to tblTasks adding the tblTasks node at the same level as tblCustomers and tblProjects, and included the ProjectID and another updg:at-identity attribute in the tblProjects node.When you pass a suitable doc into the orchestration it updates the three tables. Nice.