Retreive any complex data XML structure type from SQL using BizTalk WCF SQL Adapter

I need to retreive any type structure data from a database with WCF SQL adapter, as you know thw WCF SQL adapter retreive a particular propetary structure and it doesn’ t permit to retreive complex structure data from a database, work with complex structure type from database with WCF SQL adapter is very difficult.
For example a complex structure could be this:

Sometime I need to retreive complex XML structure data from a database, in this case I develop a pipeline component that intercept the data content in WCF  resulset.
In this way I can use any XML syntax in my storeprocedures without problem.

We can create any type of structure in a storedprocedure using XML AUTO command and affinity instructions.
When the WCF adapter retreive data returns this structure formed below


The pipeline component filter the resulset of WCF SQL adapter and retreive the  internal XML resulset as below


Where is the advantage?, in this way I can retreive any complex data structure type from database in only one step.


Generate your XML data structure in SQL and generate your schema using the Add Generated Item, Generated Schemas, Well-Formed XML, from Visual Studio
Create a root node and put alll schema content in, as below


Use the pipeline component is very simple, put the component in your stage as below


Set the properties in pipeline component


The namespace you want assign to the message

The root name node of thee message


The XPath expression to use to retreivee the internal and recursive resulset of the WCF SQL adapter
Finally create a port and use the pipeline


You can download the pipeline component HERE
[office src=”” width=”98″ height=”120″]

Related blog posts