BizTalk WCF-SQL retrieving XML column
I'm currently going through a BizTalk migration project, one of the things to migrate is the SQL Adapter to the newer WCF-SQL adapter.
One of the interesting ideas is dealing with a single XML column from SQL (e.g. a stored message or similar). I stumbled upon Richard Seroter's blog covering the same subject, with a working solution.
Another approach, and the one I've taken is to utilise the same logic behind handling FOR XML SQL queries. In such a query, you get an XML column back, which you'll note is very similar to the requirement above.
This approach is described in the above link, but essentially requires the following steps:
- The use of "Procedure" when selecting your procs in the Consume Adapter wizard
- Setting the SOAP Action mapping to use "XmlProcedure" instead of "Procedure" in the Port
- Setting the WCF binding tab to use XmlPolling (if not done already) and providing an XML Root and Namespace
- Setting your result schema (wrapped in whatever you popped in the above step) as the response message type in your Orchestration
This will give you your XML message typed directly into BizTalk. Be aware of qualified/unqualified element default form as the WCF-Adapter will "intelligently" muck about with your namespacing in the result set.