Tricks with Schemas Part 1: De-batching from a SQL Receive Port

Posted: September 25, 2008  |  Categories: BizTalk Uncategorized
Tags: BizTalk

“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;

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

BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More

Manage and monitor serverless
components effortlessly

Learn More
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top