Edit

Message Schemas for Composite Operations

  • Article

The Microsoft BizTalk Adapter for SQL Server enables you to execute composite operations on the SQL Server database. A composite operation can contain multiple operations including the Insert, Update, and Delete operations on the tables and views, and operations on stored procedures. A composite operation can include these operations in any order.

For more information about:

Message Structure for the Composite Operation

Since a composite operation contains multiple individual operations; the message structure of a composite operation contains message structures of the individual operations. As a composite operation contains operations on tables, views, and stored procedures, the composite operation message follows a request-response message exchange pattern.

The following table shows the structure of the request and response messages of a composite operation that contains an Insert operation, a stored procedure that does not take any input parameters, and a Delete operation.

Operation XML Message
Composite Operation Request <?xml version="1.0" encoding="utf-8" ?> <Request xmlns="http://[PROJECT_NAME].[COMPOSITE_SCHEMA_NAME]"> <Insert xmlns="http://schemas.microsoft.com/Sql/2008/05/TableOp/[SCHEMA]/[TABLE_NAME]"> <Rows> <[TABLE_NAME]> <[FIELD1_NAME]>[Value1]</[FIELD1_NAME]> <[FIELD2_NAME]>[Value1]</[FIELD2_NAME]> … </[TABLE_NAME]> </Rows> </Insert> <[SP_NAME] xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/[SCHEMA]" /> <Delete xmlns="http://schemas.microsoft.com/Sql/2008/05/TableOp/[SCHEMA]/[TABLE_NAME]"> <Rows> <[TABLE_NAME]> <[FIELD1_NAME]>[Value1]</[FIELD1_NAME]> </[TABLE_NAME]> </Rows> </Delete> </Request>
Composite Operation Response <?xml version="1.0" encoding="utf-8" ?> <RequestResponse xmlns="http://[PROJECT_NAME].[COMPOSITE_SCHEMA_NAME]"> <InsertResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TableOp/[SCHEMA]/[TABLE_NAME]"> <InsertResult> <long>[value]</long> </InsertResult> </InsertResponse> <[SP_NAME]Response xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/[SCHEMA]"> <[SP_NAME]Result> <DataSet> <any>[Value]</any> <any>[Value]</any> … </DataSet> </[SP_NAME]Result> <ReturnValue>[value]</ReturnValue> </[SP_NAME]Response> <DeleteResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TableOp/[SCHEMA]/[TABLE_NAME]"> <DeleteResult>[value]</DeleteResult> </DeleteResponse> </RequestResponse>

[PROJECT_NAME] = Name of the BizTalk project that contains the composite operation schema.

[COMPOSITE_SCHEMA_NAME] = Name of the composite operation schema given by the user.

[SCHEMA] = Collection of SQL Server artifacts; for example, dbo.

[TABLE_NAME] = Name of the table; for example, Employee.

[FIELD1_NAME] = Table field name; for example, NAME.

[SP_NAME] = The stored procedure to be executed; for example, ADD_EMP_DETAILS.

Message Action for the Composite Operation

The message action for the composite operation is “CompositeOperation.”

See Also

Messages and Message Schemas for BizTalk Adapter for SQL Server