Message Schemas for Insert, Update, Delete, and Select Operations on Tables and Views
The Microsoft BizTalk Adapter for SQL Server surfaces Insert, Update, Delete, and Select operations for each table and view in the SQL Server database. These operations perform the appropriate SQL statement qualified by a WHERE clause. The SQL adapter uses strongly-typed records and record sets in these operations.
Message Structure for Table Operations
The following table shows the XML message structure for the basic table operations exposed by the SQL adapter on SQL Server database tables. The target table for an operation is specified in the message action and also appears in the target namespace.
| Operation | XML Message | Description | SQL Executed by the Adapter |
|---|---|---|---|
| Insert | <Insert xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <Rows> <[TABLE_NAME]> <[FIELD1_NAME]>value1</[FIELD1_NAME]> <[FIELD2_NAME]>value2</[FIELD2_NAME]> … </[TABLE_NAME]> </Rows> </Insert> |
Inserts the supplied record set of strongly-typed data into the target table. | INSERT INTO TABLE_NAME (FIELD1_NAME, FIELD2_NAME, …) VALUES (value1, value2, …); |
| Insert Response | <InsertResponse xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <InsertResult> <long>[Value]</long> </InsertResult> </InsertResponse> |
The Insert Response message contains an array of Long data type. The array stores the identity values of the inserted rows, if any. If there is no identity column in a table, the return value is NULL. | -- |
| Select | Selecting all records:<Select xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <Columns>*</COLUMNS> <Query></Query> </Select>Selecting specific columns in a set of records: <Select xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <Columns>[COLUMN_list]</COLUMNS> <Query>where [WHERE_clause]</Query> </Select>Updating records as part of the Select Operation: <Select xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <Columns>[COLUMN_list]</Columns> <Query>where [WHERE_clause];UPDATE [TABLE_NAME] SET [FIELD1_NAME] = [value1] where [WHERE_clause]</Query> </Select> |
A SELECT query is performed on the target table using the WHERE clause specified in the element. The result set contains the columns in the comma-separated list of column names specified in the <Columns> element. It is mandatory to provide value in the <Columns> element. If all columns have to be retrieved in a table or view, * must be specified in the <Columns> element. If specific columns have to be retrieved, the column names must be separated by comma, and specified in the same order as they are defined in the table or view. It is mandatory to include the WHERE clause in the SELECT statement. If you do not want to specify a WHERE clause, you can either delete the <Query> element or leave it empty. You can update records using the Select operation. An UPDATE statement is placed in the <Query> element of the SELECT request XML, separated from the WHERE clause by a semi-colon. Note that the UPDATE statement does not operation on the result set of the SELECT statement. |
Selecting all records:SELECT * FROM [TABLE_NAME] WHERE [WHERE_clause];Selecting specific columns in a set of records: SELECT [COLUMN_list] FROM [TABLE_NAME] WHERE [WHERE_clause];Updating records as part of the Select Operation: SELECT [COLUMN_list] FROM [TABLE_NAME] WHERE [WHERE_clause]; UPDATE [TABLE_NAME] SET [FIELD1_NAME] = value1 [WHERE_clause]; |
| Select Response | <SelectResponse xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <SelectResult> <[TABLE_NAME]> <[FIELD1_NAME]>[value1]</[FIELD1_NAME]> <[FIELD2_NAME]>[value2]</[FIELD2_NAME]> … </[TABLE_NAME]> </SelectResult> <SelectResponse> |
The strongly-typed result set generated by the SELECT query. | -- |
| Update | <SelectResponse xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <SelectResult> <[TABLE_NAME]> <[FIELD1_NAME]>[value1]</[FIELD1_NAME]> <[FIELD2_NAME]>[value2]</[FIELD2_NAME]> … </[TABLE_NAME]> </SelectResult> </SelectResponse><Update xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <Rows> <RowPair> <After> <[FIELD1_NAME]>[value1]</[FIELD1_NAME]> <[FIELD2_NAME]>[value2]</[FIELD2_NAME]> … </After> <Before> <[FIELD1_NAME]>[value3]</[FIELD1_NAME]> <[FIELD2_NAME]>[value4]</[FIELD2_NAME]> … </Before> </RowPair> </Rows> </Update> |
Take an array of record pairs as input. Each record pair is a collection of two strongly-typed records: First record (in the <After> element) corresponds to new values that need to be updated.Second record (in the <Before>) corresponds to the old values of the rows. |
UPDATE [TABLE_NAME] SET [FIELD1_NAME] = value1, [FIELD2_NAME] = value2, … WHERE [FIELD1_NAME] = value3, [FIELD2_NAME] = value4, …; |
| Update Response | <UpdateResponse xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <UpdateResult>[rows updated]</UpdateResult> </UpdateResponse> |
The number of rows updated is returned in the UpdateResult element. | -- |
| Delete | <Delete xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <Rows> <[TABLE_NAME]> <[FIELD1_NAME]>value1</[FIELD1_NAME]> <[FIELD2_NAME]>value2</[FIELD2_NAME]> … </[TABLE_NAME]> </Rows> </Delete> |
-- | DELETE FROM [TABLE_NAME] WHERE [FIELD1_NAME] = value1, [FIELD2_NAME] = value2, …; |
| Delete Response | <DeleteResponse xmlns="[VERSION]/TableOp/[SCHEMA]/[TABLE_NAME]"> <DeleteResult>[rows deleted]</DeleteResult> </DeleteResponse> |
The number of rows deleted is returned in the DeleteResult element. | -- |
[VERSION] = The message version string; for example, http://schemas.microsoft.com/Sql/2008/05.
[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.
[COLUMN_list] = Comma-separated list of columns; for example, Name, Age, Designation.
[SELECT_query] = A SQL SELECT statement specified in the QUERY element of a Bulk Insert operation; for example, "SELECT * from MyTable"
[WHERE_clause] = WHERE_clause for the SELECT statement used for the operation; for example, ID > 10.
Important
The message structure for the basic table operations on views is the same as that on tables except that the view replaces the table: Insert xmlns="[VERSION]/ViewOp/[SCHEMA]/[VIEW_NAME]".
Message Actions for Basic Table Operations
The following table shows the message actions that are used by the SQL adapter for the basic table operations on tables. The SQL adapter uses the table name specified in the message action to determine the target table of the operation.
| Operation | Message Action | Example |
|---|---|---|
| Insert | TableOp/Insert/[SCHEMA]/[TABLE_NAME] | TableOp/Insert/dbo/Employee |
| Insert Response | TableOp/Insert/[SCHEMA]/[TABLE_NAME]/response | TableOp/Insert/dbo/Employee/response |
| Select | TableOp/Select/[SCHEMA]/[TABLE_NAME] | TableOp/Select/dbo/Employee |
| Select Response | TableOp/Select/[SCHEMA]/[TABLE_NAME]/response | TableOp/Select/dbo/Employee/response |
| Update | TableOp/Update/[SCHEMA]/[TABLE_NAME] | TableOp/Update/dbo/Employee |
| Update Response | TableOp/Update/[SCHEMA]/[TABLE_NAME]/response | TableOp/Update/dbo/Employee/response |
| Delete | TableOp/Delete/[SCHEMA]/[TABLE_NAME] | TableOp/Delete/dbo/Employee |
| Delete Response | TableOp/Delete/[SCHEMA]/[TABLE_NAME]/response | TableOp/Delete/dbo/Employee/response |
[SCHEMA] = Collection of SQL Server artifacts; for example, dbo.
[TABLE_NAME] = Name of the table; for example, Employee.
Important
The message action for an operation on a view is the same as that for a table except that "ViewOp" replaces "TableOp"; for example, ViewOp``/Insert/dbo/Employee_View.
See Also
Messages and Message Schemas for BizTalk Adapter for SQL Server