Message Schemas for Procedures and Functions

Message Schemas for Procedures and Functions

This topic was last updated on: June 05, 2009

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

© 2014 Microsoft Corporation. All rights reserved.
Show:
© 2016 Microsoft