Biztalk SQL Adapter

This simple SQL Adapter example. Read the article and follow the steps.
Step-1
Create Solution with name OrderManager.

Step-2
Right click the Solution and Click Add new Item and select Schema1.XSD


Step-3
Rename the Schema1.xsd to IncompleteOrder.xsd
Create the XSD structure as mentioned in below image.





Repeat the Step2 to create CompleteOrder.xsd Schema and create the schema structure as mentioned in below image.



Step-4 : create stored procedure in SQL Server.
Create procedure TestSP
(@XMLMsg ntext)
AS
Begin
DECLARE @ErrCode Int
INSERT INTO SAP_Import_Msg (iMessage_No, sMessage_XML) VALUES (1, @XMLMsg)
IF @@ERROR<>0
begin
SET @ErrCode = @@ERROR
end
ELSE
BEGIN
SET @ErrCode = 0

end

Select @ErrCode as ErrorCode for XML RAW, XMLDATA
RETURN @@ErrorEND


once you complete the Step-4 successfully, you need to extract the XSD schema in your Biztalk application.


Step-5 will explain you how to extract the schema from your stored procedure.

Step-5
Right-click on the project in the Solution Explorer and select the "Add Generated Items" option. In the list of items, select the Add Adapter option and click on the Open button. The following screen will show up:




In this screen, select the adapter of type "SQL" and click Next
In the first screen, click on the Set button and provifde the information to connect to your SQL Server instance. Select "Northwind" as the initial catalog. When the connection string is set, click Next.


The next screen shows information about the schemas that will be used to transport the information to and from SQL. In the Target Namespace option, type "http://nwtraders". In the Port-Type, select "Send Port", since we're going to send a request to SQL Server and receive a response. In the property "Request root element name", type "InCustomer", and in the property "Response root element name", type "OutCustomer". The screen should be as in the picture below:



Click on the Next button. In the screen "Statement type information", select the option "Stored Procedure". Click Next. In the combo box for selecting the procedure TestSP.



This information will be used by the SQL adapter to generate the initial schema, they are not used later in the project. Click on the Next button, and in the next screen, click the Finish button. A new schema and a new orchestration will be created in your project.

The created schema (SQLService) contains the request and response information for the stored procedure. The orchestration contains some types (port type) used to call the SQL adapter.



Step-6
Once you compelte the step-5 successfully open your mapping sheet and select Incomplete order Schema in Source side and TestSP Schema in Target Side.

Do the mapping as mentioned below.







Step-7
write the below code before you use the Transform Shape.
TempXMLDocument =new System.Xml.XmlDocument();
TempXMLDocument.LoadXml("");
InboundOrder=TempXMLDocument;

vXMLSPDoc = new System.Xml.XmlDocument();
vXMLSPDoc = InboundMsg;InboundMsg.TestSP.XMLMsg=vXMLSPDoc.InnerXml;

once you complete the above steps you can Deploy the Orchestation. You can youse the below Sample Test File.

Sample Input Values

< ns0="http://OrderManager.IncompleteOrder"> <>1 <>2005-03-01 <>ALFKI

If you need any more details mail me @ raj.webjunky@yahoo.com