How to invoke Stored Procedures with the FOR XML clause using BizTalk Adapter Pack 2.0 if the Stored Procedures are not in the default schema?

• Download samples - 10.4 KB

Introduction

I prepared this document when I was working with a WCF adapter. I think this will be helpful to work on invoking Stored Procedures with the FOR XML clause using BizTalk Adapter Pack 2.0 if the Stored Procedures are not in the default schema.

Create the Stored Procedure

Create the Stored Procedure for getting the employee details as shown below. The table and the SP are under the schema [bt].

CREATE PROCEDURE [bt].[Get_EmployeeDetails] 

AS 

BEGIN 

    SET NOCOUNT ON; 

    SELECT Emp_Id,Emp_Name,Designation,[Address] FROM bt.Tbl_Employee 

    FOR XML AUTO,XMLSCHEMA 

    END

Create the BizTalk project

Generate the schema for the request message for the Stored Procedure

Generate the schema for the Request Message for the Stored Procedure with the FOR XML clause using the WCF-based SQL Adapter. This provides the schema for the request message that the adapter sends to SQL Server to invoke the Stored Procedure. Below are the steps for creating the schema.

Generate the schema for the response message for the Stored Procedure

Generate the schema for the response message for the Stored Procedure with the FOR XML clause. Execute the Stored Procedure to get the schema for the response message. For this, execute the SP in SQL Management Studio and take the schema structure (content with the tag <xsd:schema>) and save as a file with the extension "xsd".

Open the schema in Visual Studio and make the following changes:

1. Add a node to the schema and move the existing root node under this newly added node. Give a name to the root node. For this topic, rename the root node to Root.
2. The schema generated for the Stored Procedure references a sqltypes.xsd. You can get the sqltypes.xsd schema from http://go.microsoft.com/fwlink/?LinkId=131087. Add the sqltypes.xsd schema to the BizTalk project.

In the schema generated for the Stored Procedure, change the value of import schemaLocation to the following:

import schemaLocation="sqltypes.xsd"

Provide a target namespace for the schema

Click the <Schema> node, and in the Properties pane, specify a namespace in the Target Namespace property. Give the namespace as per your project: http://ForXmlStoredProcs/namespace.

Change the property

Click the <Schema> node, and in the Properties pane, change the property "Element FromDefault" from "Qualified" to "(Default)".

Before

After

Change the schema property

Click on the root node that is created from the SP and then change the "Min Occurs" and "Max Occurs" properties to "0" and "unbounded", respectively, as shown below.

9. Defining messages and message types

The schema that you generated earlier describes the "types" required for the messages in the orchestration. A message is typically a variable, the type for which is defined by the corresponding schema. You must now create messages for the orchestration, and link them to schemas that you generated in the previous step.

To create messages and link to the schema

1. Add an orchestration to the BizTalk project. From Solution Explorer, right-click the BizTalk project name, point to Add, and then click New Item. Type a name for the BizTalk orchestration, and then click Add.
2. Open the Orchestration View window of the BizTalk project, if it is not already open. To do so, click View, point to Other Windows, and then click Orchestration View.
3. In Orchestration View, right-click Messages and then click New Message.
4. Properties window.
5. In the Properties pane for Message_1, do the following: select the schema generated by the Consume Adapter Service as the message type of this message.



Fig. 14
6. Create one more new message. In the Properties pane for the new message, do the following: select the schema generated from the Stored Procedure as the message type of this message.



Fig. 15

Setting up the orchestration

You must create a BizTalk orchestration to use BizTalk Server for executing a Stored Procedure in SQL Server. In this orchestration, you drop a request message at a defined receive location. The SQL Adapter consumes this message and passes it on to SQL Server. The response from the SQL Server is saved to another location. You must include Send and Receive shapes to send messages to SQL Server and receive responses, respectively. A sample orchestration for invoking a procedure resembles the following:

1. Adding message shapes

Make sure you specify the following properties for each of the message shapes. The names listed in the Shape column are the names of the message shapes as displayed in the just-mentioned orchestration.

Shape	Shape Type	Properties
ReceiveMessage	Receive	Set Name to ReceiveMessage Set Activate to True
SendMessage	Send	Set Name to SendMessage
ReceiveResponse	Receive	Set Name to ReceiveResponse Set Activate to False
SendResponse	Send	Set Name to SendResponse

2. Adding ports

Make sure you specify the following properties for each of the logical ports. The names listed in the Port column are the names of the ports as displayed in the orchestration.

Port	Properties
MessageIn	Set Identifier to MessageIn Set Type to MessageInType Set Communication Pattern to One-Way Set Communication Direction to Receive
LOBPort	Set Identifier to LOBPort Set Type to LOBPortType Set Communication Pattern to Request-Response Set Communication Direction to Send-Receive
ResponseOut	Set Identifier to ResponseOut Set Type to ResponseOutType Set Communication Pattern to One-Way Set Communication Direction to Send

3. Specify messages for Action Shapes, and connect them to ports

The following table specifies the properties and their values that you should set to specify messages for action shapes and to link the messages to the ports. The names listed in the Shape column are the names of the message shapes as displayed in the orchestration mentioned earlier.

Shape	Properties
ReceiveMessage	Set Message to Request Set Operation to MessageIn.FOR_XML.Request
SendMessage	Set Message to Request Set Operation to LOBPort.FOR_XML.Request
ReceiveResponse	Set Message to Response Set Operation to LOBPort.FOR_XML.Response
SendResponse	Set Message to Response Set Operation to ResponseOut.FOR_XML.Request

After you have specified these properties, the message shapes and ports are connected and your orchestration is complete.

Configuring the BizTalk application

After you have deployed the BizTalk project, the orchestration you created earlier is listed under the Orchestrations pane in the BizTalk Server Administration console. You must use the BizTalk Server Administration console to configure the application.

Configuring an application involves:

• Selecting a host for the application.
• Mapping the ports that you created in your orchestration to physical ports in the BizTalk Server Administration console. For this orchestration, you must:
• Define a location on the hard disk and a corresponding file port where you will drop a request message. The BizTalk orchestration will consume the request message and send it to SQL Server database.
• Define a location on the hard disk and a corresponding file port where the BizTalk orchestration will drop the response message containing the response from the SQL Server database.
• Define a physical WCF-Custom or WCF-SQL send port to send messages to the SQL Server database. For creating the SQL Send and Receive ports, we can use the binding file generated when the schema for the Stored Procedure was generated by the adapter.



Fig. 17
• You must also specify the Action in the Send port. For procedures that contain the FOR XML clause, you must set the Action in the following format:

XmlProcedure/<schema_name>/<procedure_name>



Fig. 18



Fig. 19



Fig. 20

Here you have to specify the Stored Procedure that is supposed to execute, and the "InoboudOperationType" should be "XmlPolling".



Fig. 21
• specify the database credential.

The input trigger message will be as shown below:



Fig. 22