XML in Stored Procedures

In the last post we discussed a case study which stated that in BizTalk XML is the communication language.

Here I would like to add, how to use XML in the Stored Procedures? With the intruduction of SQL Server 2005, Microsotf added support for XML. Now an XML document can be passed in the stored procedure and then we can do extract the data from the XML document after getting its Handle. In the same manner Stored Procedures can now return XML.

When we wanted the order generated to be passed to the orchestration in BizTalk, we wanted that to be in XML format, and to get that in the XML format we have to write the stored procedure in such a manner that it would return XML document to us.

FOR XML Clause:
Now, when you write a 'Select' statement in a stored procedure you can use 'FOR XML' clause to generate the XML of the recordset returned from the select query.
e.g.
SELECT fieldname1, fieldname2 FROM tablename FOR XML AUTO

The XML returned from the stored procedure can now be recieved at a recieve location where the SQL Adapter is configured in the BizTalk Orchestration. Once the receive location gets the XML document it is passed to the Receive PORT.

For further information on SQL usage in Stored Procedures, please visit the links available.

In the next post I would post a image of an Orchestration created for a real word application and would describe every component of it.

Case Study Contd...

Use of Biztalk in the Central Order Generation System:
In the central order generation system, the order gets generated and must be served to the subscribing systems. These subscribing systems could be of any platform with any ERP, e.g. SAP, etc. There has to be a platform independent way to transfer the messages to the systems and receive acknowledgement as well. Here comes the role of BizTalk.

In our case we have out the entire order generation logic in the stored procedure which create an order from the database and return the Order in a well formed XML format. Many people still wonder why XML?, the answer to it is that XML is the only data type rather format which has been supported by almost every system/platform existing today. Once we get an XML Order we need to expose a web service which could when called could serve this order to the subscribing system.

Think of the days when were doing this coding our way through, how much do we think we would have spent on creating an application which would take the order from the SQL Stored Procedure, apply some business logic in it, get the transactions going, create some statistical report based on it and eventually exposing it as a web service. BizTalk makes this just a game of configuring various components provided in it.

Create an Orchestration with a SQL Adapter configured to receive data from the SQL Stored Procedure, use some transformation shapes to transform the message, use pipelines and maps to decide the flow, and in the end create a SOAP adapter for receiving the request and serving the Order. BizTalk has inbuilt support for transaction which could be long running and atomic and several other features.

Management can use BAM provided by Biztalk and see the orders moving through the system. Developers have got the BizTalk Administration console to configure Orchestrations and ports and location to receive. BizTalk reduces the development time considerably and lets the solution developers focus on "What to do?" rather than thinking "How to do?".

In the next post we would discuss about Use of XML in Stored Procedures and consuming the result of stored procedures in BizTalk Orchestration and eventually exposing that as a web service.

Case study – Central Order Generation System.

We would discuss a scenario where a pallet manufacturing company would rent its pallet to different businesses and would eventually bill the businesses depending on the number of pallets they are holding on to.

For ease of use let us use names for companies:-
Pallet Manufacturing Company – CompPallet
Soft Drink Manufacturer – CompSoftDrink
Retail Store – CompRetail

We would discuss two other technologies used here which would be RFID and SSL.

CompPallet produces pallet and labels them with RFID (Radio Frequency Identification) tags for easier and smoother tracking. CompSoftDrink is a user of pallets and CompRetail as well uses pallets for various purposes.

Because CompPallet is renting pallets to both the companies and both the companies are also involved in a business between them as CompSoftDrink is supplying soft drinks to CompRetail for selling in the market, they all need to keep track of how many pallets they are holding on to at a particular time so that they can account for them properly, secondly CompPallet also needs to know which company is holding on to how many number of pallets so that it can bill them properly.

To get this working in coordination and without any hassle there has to be system which could keep a track of everything and can provide strategic reports to all the 3 companies involved.
The central order generation system provides a solution; it generates orders with Order Header and sends it across to the 2 companies, these companies fill these orders with details from their WMS (Warehouse Management Systems), details here would be the Pallet Ids which they are holding on to and which can scanned using RFID scanner as they were labeled at the time of the manufacturing and send the order back to the central system.

Central system provides a web based portal where the clients can login and see strategic reports. These reports tell the exact location of the pallets and help all the businesses involved in monitoring their supply chain.

Next post would be about how we can use BizTalk Server in creating and maintaining this central order generation system.

Central Order Generation System for SCM

The central order generation system is responsible for generating an order which would be sent to Business 'A' which would compelet this order in return and would send back the completed order to the central server.

The order sent by the central server contains header information like Order#, OrderDate, etc and its status, initially it can be set to 'new' as the receiver recieves it in the incomplete new order to complete. Once the order reaches the server at Business 'A', it completes the order by filling in the details of the items to track and sends it back to the central server. Now, the central server generates a report for the Business which actually produced that item, and that Company or business can view where are the items produced by them right now, which company is holding on to them.

Use of BizTalk:

BizTalk can be used to develop a solution which would take orders generated from the SQL Stored Procedures using SQL Adapter and would transform them to an XML message. Many more business rules can be applied by creating orchestrations. Finally, that orchestration can be exposed as a web service to the clients for serving new orders.

This web service can be conventional asp.net 2.0 service (asmx) or can be a WCF service in case of biztalk server 2006 R2 which has a support for .net 3.0.

Usefulness of BizTalk to the businesses:

Biztalk provides BAM Portal, its Business Activity monitoring portal, the managers need not go in the technical details of the process, they will have a web based ready made portal to their disposal for taking startagic decisions.

Other technologies :

Other technologies which can be used here could be SSL. SSL or https can be used to make the transmission more robust and secure. That means the web services exposed from BizTalk server would be hosted over https not conventional http.

We would discuss a case study on this tomorrow. Your commnets are most welcome.

Topics in the days to come...

From tomorrow, We will discuss the details of all the components of a Supply Chain Management Solutions taking one component every day, we would discuss a real world case of SCM solution implementation. We will start from:-

• Central Order Generating System functionality.
• Uses of BizTalk in it.
• Usefulness of BizTalk services to the Business.
• Other technologies that work in conjunction with BizTalk.

I invite you all to share your knowledge and experience through this blog.

RFID with BizTalk

Components of Supply Chain Management Solutions

Supply Chain Management Solutions mainly have the following comoponents:-

• Central Order Generation System
• Robust Routing System
• Business Component Systems - For Message transformations & Business Activity Monitoring
• User Interface Systems - For user input or gathering input through sensors.
• Acknowledgement System - For acknowledging every message sent accross various systems.