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.
