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:

DETAILS
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

Capture0

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

Capture01

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

Steps:

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

schemaelaboratodapipe

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

Capture

Set the properties in pipeline component

proppipe

Namespace:
The namespace you want assign to the message

RootName:
The root name node of thee message

WcfXmlPath:

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

pipeporta

You can download the pipeline component HERE
[office src=”https://skydrive.live.com/embed?cid=8FD644DFB7484C68&resid=8FD644DFB7484C68%21527&authkey=ALUP1nx5hDEIxoE” width=”98″ height=”120″]

Related blog posts