Throughout my BizTalk career, I’ve come across some ludicrously complex ways to solve problems that actually work straight out the box. It seems to be a common ‘easy fix’ for developers to just fall back on Custom Pipelines to solve any small problem. The following is one of those problems with a simple solution that seems to have been lost into the vast abyss of the web.
It should also stop developers from simply consuming a table using the Biztalk ’Add Generated Items - Consume Adapter Service’ wizard, which works, but you are stuck with a flat xml structure.
So, How do we get a complex XML structure output from a SQL stored Procedure.
This is actually the easy part. It’s quite simple to have a Stored Procedure return an XML result set, for those of you that don’t know, google ‘For XML’.
When you view the StoredProcedure output in SQL, all looks well. The whole XML will be in a single cell, with a static Column Name ‘XML_F52E2B61-18A1-11d1-B105-00805F49916B’. Do not worry about the name, as it is static.
The problem is when use a WCF adapter and consume as a TypedProcedure (or even NonTyped), it returns the following structure below; Escaping the characters and splitting the result set into two to three segments.
This is where consensus normally splits. Someone has developed a Custom Pipeline to both unescape and combine the pieces, to give you the beautiful xml structure that most developers are used to.
This works and seems to be what is normally discovered and actioned when you try to search the complex inter-web for a solution. However, you don’t need to link to it, there is a simpler way!
Everyone seems to have heard of TypedPolling, however, the solution here is aptly named ‘XMLPolling’.
Set the following three settings in the Send Port Bindings:
And the action to XmlProcedure;
Action="XmlProcedure/dbo/ProcedureName"
Now you have a complex xml stored procedure consumed into Biztalk as an XML. No Custom Pipeline needed. Just minor tweaking to a send port.