Using Windows Azure BizTalk Services to Insert XML Messages into an Azure SQL Server

Introduction

This Article provides directions on the way to tack a BizTalk Services resolution that receives a XML message, processes it through a XML bridge deployed below the BizTalk Services subscription on Windows Azure, and so inserts the message into an Azure SQL Server database

Business Scenario

Fabrikam and Contoso are two business partners. Fabrikam (the retailer) sends sales order messages to Contoso (the supplier). Contoso maintains all the sales order data in table called SalesOrders, which is hosted on Azure SQL Server database. Fabrikam sends XML messages to Contoso. Hence, Contoso has to implement a solution on Windows Azure, which enables the following:

       •    Contoso must pull the XML messages at which Fabrikam drops the sales order messages.
       •    Contoso must process the message received from Fabrikam and map it to the message   for inserting the sales order in its   SQL    Server database

To enable this scenario, Contoso does the following:

• Generates the schema of the XML message that it will receive from Fabrikam.
• Configures an XML One-Way Bridge as part of the BizTalk Service project to enable message validation and transformation. This bridge takes a XML message, validates it against the schema generated earlier, and then transforms it to the schema required to enter a message into the SQL Server database.
• Uses BizTalk Adapter Service to connect to the Azure SQL Server database from the XML One-Way Bridge deployed under the BizTalk Services subscription.

Step by Step

Create a Biztalk Service project.

 

Create XML schema that will parse the xml send by fabrikam.

Create Table on Azure SQL Database where the sales order data will be inserted.

Login to azure Sql Portal

 Execute the script in the new query.

CREATE TABLE SalesOrder(

 CompanyCode nvarchar(50),

 PartID nvarchar(50),

 Quantity nvarchar(50),

 AskPrice nvarchar(50),

 RequestShipmentDate dateTime,

 AddressLine1 nvarchar(100),

 AddressLine2 nvarchar(100),

 AddressCity nvarchar(100),

 State nvarchar(100),

 Country nvarchar(100),

 Zipcode nvarchar(100),

 Contact nvarchar(100),

 Firstname nvarchar(100),

 Lastname nvarchar(100),

 Comments nvarchar(500),

 PlacedDate DateTime)

  

 CREATE CLUSTERED INDEX SalesOrder_CC ON SalesOrder(CompanyCode)

Create an LOB Relay and an LOB Target for the Insert operation on the SalesOrder table.
Go to server explorer and Biztalk Adapter services.

Click on Add Sql Target; specify the connection properties for Azure Sql database.

 Define the service bus configuration where relay will be deployed.

Generate target schema and add to Biztalk service project.

LOB schema added to project 

Create Map that transformed SalesOrder XML 

Drag and drop an XML One-Way Bridge from toolbox to the Bridge Configuration surface
Drag and drop the relay created in previous steps from Biztalk Adapter Services.
Connect Bridge to LOB relay.      

Set the Route action so that the outgoing message to the LOB application has a SOAP action header
Open Server Explorer and navigate to the SQL Server LOB Relay we created earlier. Right click the relay, click Properties, and for the Operations property, copy the value of the first operation

In the Route Actions dialog box, click Add to open the Add Route Action dialog box. In the Add Route Action dialog box

Configure XML Bridge.
Double click the bridge and set message type to Salesorder schema
In the transform stage select the map.

Test the solution

Use Windows Azure Biztalk Services Explorer to send test message to XML Bridge.

Load the test file and send.

Check the data on portal