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.
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
Use the pipeline component is very simple, put the component in your stage as below
Set the properties in pipeline component
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
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″]