SQL Server 2005 offers many exciting new features. One of them is the support for Xml as a data type. On the surface, this looks great since BizTalk likes Xml. But, we come to learn that the Xml Data Type is not supported using the SQL Adapter inside BizTalk.
But, with a little effort you are easily able to accept Xml Documents that are all in one cell from SQL Server into BizTalk using the Receive Adapter.
Here is the scenario: The client is using a SQL table as a type of queue. Messages are writing as full Xml Documents into a single cell of the table, along with an id, datetime, and a status. This data needs to be inserted into BizTalk. In the past, I have used a windows service to send the messages to a MSMQ queue and then into BizTalk. My new approach is to use a simple stored procedure that I call from a Receive Port to extract out the Xml and update the states on the table.
In addition, you can use one Receive Location to receive many different message types. Then you can use message box routing (Direct Binding) to route many different message types to Send Ports or Orchestrations. You can also map on the Receive Port if needed.
The downside is you can not auto generate the schema using the SQL Schema Generation Wizard. You will need to generate them manually based on the Xml messages you will be putting into your queue table.
I have put together a sample complete with a sample database, stored procedure, and BizTalk code.
Download: Working with SQL 2005 XML Data in BizTalk 2006 Sample
This includes an importable MSI and Binding files if you want to build the code yourself. See the readme.txt file for more set up information.