WCF Send Port that Returns XML responses, CORRECTLY

Do you have a need to call out to SQL and obtain a set of results, typically this is done via the wizard, and typically it’s a flat structure, in that a single repeating list is sent.

What if you wanted more of a structured response, where the repeating list, has a repeating list within this, you can return multiple result sets, and have some kind of way of matching up the results, and create a rather complex map to do this, what if you have several of these structures, your timeframes to create this will blow out, and maintenance of this is extreme.

Did you know that SQL can produce this repeating, rather XML looking structure for you, with great ease?

This is one I have come across on several occasions, so I decided to blog it.

I will use a simple example to show you how to do this, and why it’s so much simpler.

I have a stored procedure, that accepts two parameters, [GetLoadTest] @Users,@Run

When using the wizard, I get this schema. The response is a typical stored procedure result set, of which is of limited use.

I am returning this:

 

The Schema for this is a repeating structure, with a child repeating structure. This is not easily going to work here. There is a funky xpath, xml object method which is both bad for performance, and requires code in an orchestration to make this work, and highly not recommended.

What if you could just have the message returned, actually return you message, as per your schema below. Well you can..... here is how.

 

Firstly my stored procedure that gets the results:

This will now return XML in my XML structure, as shown earlier, notice the NAMESPACE....

This is the schema for just this message, of which the Standard xml definition wizard can whip up for you.

 

Here is where the first part of magic occurs, you take the schema that the SQL wizard generated and modify the response node.

You add a reference to the LoadTestRecord Schema. Then you simply change the response message to be of this type, repeating, as below.

 

The next bit of magic is a pipeline component, these are typically used to turn the horrid things people send us into more meaningful things.

I am not going to bore you with the details, but the response that the WCF-SQL adapter give us, for a XML response message is not very nice, with CDATA nodes, and encoded XML content.

So I wrote a simple pipeline that will decode this mess and replace the StoredProcedureResultSet0 node, this is configurable in the properties of the pipeline...

The source code is attached here: Link TBA

Then it’s a simple as configuring your send port to use the pipeline. Create a pipeline that uses the pipeline component that I have, and the standard XML Disassembler pipeline component.

 

The send port

 

Select the receive pipeline.

Be sure to set the SOAP action.

 

Then your orchestration or even message subscription will receive the exact message that is specified in the schema, and mapping can take place on all the repeating nodes of repeating nodes.

VERY handy for client detail, further detail and extra detail, all of which are repeating.

You can use the standard out of box functionality, but this was a MUCH easier way of doing this, and the mapping was a breeze.

SQL does this as part of built in functionality and makes life a great deal easier.

It would however, as a NOTE TO THE PRODUCT GROUP... be much easier if there was a checkbox on the adapter properties, to say I am sending a XML response, and work out for me the schema based on the response, and not just default encode it in a very horrible way.