Edit

Message Schemas for the ExecuteNonQuery, ExecuteReader, and ExecuteScalar Operations

  • Article

The Microsoft BizTalk Adapter for SQL Server exposes the ExecuteNonQuery, ExecuteReader, and ExecuteScalar outbound operations at the root level to execute any arbitrary SQL statements in SQL Server.

For more information about:

Message Structure for the ExecuteNonQuery, ExecuteReader, and ExecuteScalar Operations

The messages in these operations follow a request-response message exchange pattern, and the following table shows the structure of these request and response messages.

Operation XML Message Description
ExecuteNonQuery Request <ExecuteNonQuery xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/"> <Query>[PL/SQL STATEMENT1];[PL/SQL STATEMENT2];…</Query> </ExecuteNonQuery> Within the <Query> tag, you can specify multiple PL/SQL statements separated by a semi-colon.
ExecuteNonQuery Response <?xml version="1.0" encoding="utf-8" ?> <ExecuteNonQueryResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/"> <ExecuteNonQueryResult>[value]</ExecuteNonQueryResult> </ExecuteNonQueryResponse> For the UPDATE, INSERT, and DELETE statements, [value] represents the number of rows affected by the PL/SQL statements in the ExecuteNonQuery Request message. For all other types of statements, [value] is -1.
ExecuteReader Request <ExecuteReader xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/"> <Query>[PL/SQL STATEMENT1];[PL/SQL STATEMENT2];…</Query> </ExecuteReader> Within the <Query> tag, you can specify multiple PL/SQL statements separated by a semi-colon.
ExecuteReader Response <?xml version="1.0" encoding="utf-8" ?> <ExecuteReaderResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/"> <ExecuteReaderResult> <DataSet> <Any>[value]</Any> <Any>[value]</Any> … </DataSet> </ExecuteReaderResult> </ExecuteReaderResponse> The result set is the response message of the PL/SQL statements executed in the ExecuteReader Request message, and is returned as an array of DataSet. For information about DataSet, see “DataSet Class” at https://go.microsoft.com/fwlink/?LinkID=196853.
ExecuteScalar Request <ExecuteScalar xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/"> <Query>[PL/SQL STATEMENT1];[PL/SQL STATEMENT2];…</Query> </ExecuteScalar> Within the <Query> tag, you can specify multiple PL/SQL statements separated by a semi-colon.
ExecuteScalar Response <?xml version="1.0" encoding="utf-8" ?> <ExecuteScalarResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/"> <ExecuteScalarResult>[value]</ExecuteScalarResult> </ExecuteScalarResponse> The [value] represents the value in the first column of the first row in the result set returned by the PL/SQL statements in the ExecuteScalar Request message.

[PL/SQL STATEMENT] = The entire PL/SQL statement to be executed.

Message Action for the ExecuteNonQuery, ExecuteReader, and ExecuteScalar Operations

The following table shows the message actions that are used by the ExecuteNonQuery, ExecuteReader, and ExecuteScalar operations.

Operation Action
ExecuteNonQuery Request GenericOp/ExecuteNonQuery
ExecuteNonQuery Response GenericOp/ExecuteNonQuery/response
ExecuteReader Request GenericOp/ExecuteReader
ExecuteReader Response GenericOp/ExecuteReader/response
ExecuteScalar Request GenericOp/ExecuteScalar
ExecuteScalar Response GenericOp/ExecuteScalar/response

See Also

Messages and Message Schemas for BizTalk Adapter for SQL Server