“Schemas are the soul of BizTalk. They alone create attack and defense, the way they are deployed decides the fate of the game.”
I have been doing a lot with this pattern lately and I know of two ways to do this namely;
- Walkthrough: Disassembling Result Sets Using the SQL Adapter
- De-batching Inbound Messages From BizTalk SQL Adapter
The first pattern is from Microsoft but I like the second pattern which is from a blog article by Richard Seroter. Richard’s blog is one blog I read all the time and I have stolen many ideas from it. Keep up the good work Richard. Why do I like this pattern? It works.
I found one gotcha with the ‘Seroter’ pattern. If your SQL schema has been created from a temporary table like SELECT * from #temptable FOR XML AUTO, ELEMENTS then you will get an error when you try to import using XSD include. The error happens because of the way BizTalk represent the ‘#’ symbol in XML. The solution is to not use # i.e. do something like SELECT * from #temptable AS temptable FOR XML AUTO, ELEMENTS. Interestingly enough if after you have created your entire schema you can add the # symbol and everything still works. So it is just the XSD import that has the problems.
I often find that I also have to promote or distinguish some of the elements on the resulting SQL adapter schemas and I have found the following blog article by eliasen – Promoting elements from SQL Adapter Schemas that describes an easy way to do this.
Finally if you are de-batching just so you can submit your messages to a SQL send port one by one don’t bother. See this article by Multiple Stored Procedure Calls using the BizTalk SQL Adapter without a loop shape « Connected Thoughts – Thiago Almeida
