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.