Message Schemas for Procedures and Functions

The Microsoft BizTalk Adapter for SQL Server surfaces SQL Server database stored procedures and scalar and table valued functions as operations. This section describes the message structure and actions used to invoke procedures and functions.

Message Structure of Procedures and Functions

The operations surfaced for procedures and functions follow a request-response message exchange pattern. The following table shows the structure of these request and response messages.

Operation	XML Message	Description
Stored Procedure Request	<[SP_NAME] xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/[SCHEMA]"> <[PRM1_NAME]>value1</[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[SP_NAME]>	-
Stored Procedure Response	<[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>	The return value of a stored procedure is an array of DataSet.
Strongly-Typed Stored Procedure Request	<[STRNG_SP_NAME] xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/[SCHEMA]"> <[PRM1_NAME]>value1<[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[STRNG_SP_NAME]>	-
Strongly-Typed Stored Procedure Response	<[STRNG_SP_NAME]Response xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/[SCHEMA]"> <StoredProcedureResultSet0> <StoredProcedureResultSet0 xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/ProcedureResultSets/[SCHEMA]/[STRNG_SP_NAME]"> <[PRM1_NAME]>value1<[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </StoredProcedureResultSet0> </StoredProcedureResultSet0> <ReturnValue>[Value]</ReturnValue> </[STRNG_SP_NAME]Response>	The return value of a strongly-typed stored procedure is an array of strongly-typed data.
Scalar Function Request	<[SCLR_FN_NAME] xmlns="http://schemas.microsoft.com/Sql/2008/05/ScalarFunctions/[SCHEMA]"> <[PRM_NAME]>value</[PRM_NAME]> </[SCLR_FN_NAME]>	-
Scalar Function Response	<[SCLR_FN_NAME]Response xmlns="http://schemas.microsoft.com/Sql/2008/05/ScalarFunctions/[SCHEMA]"> <[SCLR_FN_NAME]Result>return_value</[SCLR_FN_NAME]Result> <[PRM_NAME]>value</[PRM_NAME]> </[SCLR_FN_NAME]Response>	-
Table Valued Function Request	<[TBL_FN_NAME] xmlns="http://schemas.microsoft.com/Sql/2008/05/TableValuedFunctions/[SCHEMA]"> <[PRM1_NAME]>value1</[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[TBL_FN_NAME]>	-
Table Valued Function Response	<[TBL_FN_NAME]Response xmlns="http://schemas.microsoft.com/Sql/2008/05/TableValuedFunctions/[SCHEMA]"> <[TBL_FN_NAME]Result> <[TBL_FN_NAME] xmlns="http://schemas.microsoft.com/Sql/2008/05/TableValuedFunctions/[SCHEMA]"> <[PRM1_NAME]>value1</[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> ... </[TBL_FN_NAME]"> ... </[TBL_FN_NAME]Result> </[TBL_FN_NAME]Response>

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

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

[STRNG_SP_NAME] = The strongly-typed stored procedure to be executed; for example, GET_EMP_DETAILS.

[SCLR_FN_NAME] = The scalar function to be executed; for example, GET_EMP_ID.

[TBL_FN_NAME] = The table valued function to be executed; for example, TVF_EMPLOYEE.

[PRM_NAME] = The name of the SQL Server parameter.

Message Actions of Functions and Procedures

The SQL adapter uses the following message actions for stored procedure and function operations.

Message	Action	Example
Stored Procedure Request	Procedure/[SCHEMA]/[SP_NAME]	Procedure/dbo/ADD_EMP_DETAILS
Stored Procedure Response	Procedure/[SCHEMA]/[SP_NAME]/response	Procedure/dbo/ADD_EMP_DETAILS/response
Strongly-Typed Stored Procedure Request	TypedProcedure/[SCHEMA]/[STRNG_SP_NAME]	TypedProcedure/dbo/GET_EMP_DETAILS
Strongly-Typed Stored Procedure Response	TypedProcedure/[SCHEMA]/[STRNG_SP_NAME]/response	TypedProcedure/dbo/GET_EMP_DETAILS/response
FOR XML Stored Procedure Request	XmlProcedure/[SCHEMA]/[SP_NAME]	XmlProcedure/dbo/GET_EMP_DETAILS_FOR_XML
FOR XML Stored Procedure Response	XmlProcedure/[SCHEMA]/[SP_NAME]/resp	XmlProcedure/dbo/GET_EMP_DETAILS_FOR_XML/response
Scalar Function Request	ScalarFunction/[SCHEMA]/[SCLR_FN_NAME]	ScalarFunction/dbo/GET_EMP_ID
Scalar Function Response	ScalarFunction/[SCHEMA]/[SCLR_FN_NAME]/response	ScalarFunction/dbo/GET_EMP_ID/response
Table Valued Function Request	TableFunction/[SCHEMA]/[TBL_FN_NAME]	TableFunction/dbo/TVF_EMPLOYEE
Table Valued Function Response	TableFunction/[SCHEMA]/[TBL_FN_NAME]/response	TableFunction/dbo/TVF_EMPLOYEE/response

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

[STRNG_SP_NAME] = The strongly-typed stored procedure to be executed; for example, GET_EMP_DETAILS.

[SCLR_FN_NAME] = The scalar function to be executed; for example, GET_EMP_ID.

[TBL_FN_NAME] = The name of the table valued function to be executed; for example, TVF_EMPLOYEE.

See Also

Messages and Message Schemas for BizTalk Adapter for SQL Server