Of late I have been working on a BizTalk project that requires me to pick up XML data from a queue type table in a SQL Database. The table in question contains records which contain some metadata columns as well as an XML type column. Data, including the already formatted XML, is loaded into the table by an external process. BizTalk is then meant to poll for new records (it is only interested in the XML column but the stored procedure it is calling on might make use of the metadata columns), all the resulting XML data is then returned to BizTalk wrapped within an envelope which can then be debatched. This sounded like the perfect case for the WCF-SQL adapter making use of the XmlPolling InboundOperationType.
Bill Chestnut has written a fantastic blog post explaining how to use the WCF-SQL adapter with the XmlPolling InboundOperationType, the main focus of the post being on executing SQL stored procedures that form up and return XML data. Seeing as my requirements were similar though potentially simpler in that my stored procedure just needed to execute a select on the XML column in the queue table, I followed most of the instructions from Bill’s post. What I found however was that when there were multiple records being returned, even though I could confirm that the stored procedure was returning multiple records, BizTalk was only receiving the first of the returned records in the envelope.
After some fiddling, I found that if the stored procedure casts the XML column to nvarchar when selecting it then all the records will be returned in the envelope.
Of course another alternative would be to not use the XML data type for the column at all, but that would mean losing the protection that the SQL XML type provides us, ensuring that the data values are well-formed XML and even allowing for them to be validated against a schema. In my case I chose to take on the extra overhead involved in casting the XML data rather than storing it as an nvarchar (unfortunately varchar will not work, you will get an error back as follows – System.InvalidOperationException: Invalid command sent to ExecuteXmlReader. The command must return an Xml result).
If you are interested in tinkering with this then do download my example solution including the database scripts from my google drive and have a play. If anyone finds a better way to do this I am all ears.