Hi all
Some weeks ago, I had a customer that had an Order XML and needed to fetch all the
names of the ordered items based on the item number that was in the XML.
He contacted me because the solution he had thought of didn’t work. What he did was
that he mapped the Order XML to a SQL Adapter schema that called an SP in SQL Server
to get the item name based on the item number. The issue he ran into was, that the
SP got called multiple times – once for each item, and the SQL adapter didn’t seem
to batch all the results into one result for his orchestration.
So, given this simplified Order:
<ns0:Order xmlns:ns0="http://MultipleCallsToSP.Order">
<Header>
<CustomerName>CustomerName_0</CustomerName>
<OrderNumber>OrderNumber_0</OrderNumber>
</Header>
<OrderLines>
<OrderLine>
<ItemNumber>21</ItemNumber>
<Quantity>42</Quantity>
</OrderLine>
<OrderLine>
<ItemNumber>42</ItemNumber>
<Quantity>21</Quantity>
</OrderLine>
</OrderLines>
</ns0:Order>
He mapped it to this XML:
<ns0:GetItemNameRequest xmlns:ns0="http://eliasen.dk">
<ns0:GetItemName ItemID="21" />
<ns0:GetItemName ItemID="42" />
</ns0:GetItemNameRequest>
This was then sent to SQL Server using the SQL Adapter to call a SP named “GetItemName”
which just takes an ItemID (int) as parameter and returns the ItemName hat matches
the ItemID.
Now, the schema that is generated for the SQL Server request actually doesn’t allow
for multiple GetItemName elements to be created, but that is changeable 🙂 If you
set it to have maxOcurs = unbounded, then it can occur multiple times, and what happens
is that the SP is called multiple times. Unfortunately, only one of the ItemNames
is returned – the rest is ignored.
So the customer came to me because naturally, he needed all the ItemNames and not
just one of them. I have suggested 5 possible solutions, which I will describe here.
First option
Use the pattern described at http://blog.eliasen.dk/2006/11/05/LoopingAroundElementsOfAMessage.aspx to
loop around the order lines and build the resulting XML one order line at a time.
Second option
Use enveloping in the receive location in order to get one orchestration
started for each order line.
Third option
Use the Database Lookup functoid to retrieve the ItemName based on the ItemID
Fourth option
Generate a comma separated list of ItemID’s in the map, and let the stored procedure
use that list to return the relevant ItemNames. This has some consequences for the
stored procedure. Before it looked like this:
SELECT ItemNumber, ItemName
FROM Items
WHERE Items.ItemNumber = @ItemID
FOR XML AUTO, ELEMENTS
Now, it looks like this:
SELECT ItemNumber, ItemName
FROM Items
WHERE EXISTS (select * from dbo.Split(’,’,@items) where [Items].ItemNumber = ID)
FOR XML AUTO, ELEMENTS
@items is the parameter for the SP, which is just an nvarchar that is to contain the
comma separated list.
For this to work you need the Split function, which looks like this:
CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN
(
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn, CONVERT(int, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start
ELSE 512 END)) AS ID
FROM Pieces
)
In order to generate the comma separated list in your map, I have written two blog
posts about this issue, which you can find at http://blog.eliasen.dk/2009/06/22/HandlingCommaSeparatedValuesInsideAMapPartI.aspx and http://blog.eliasen.dk/2009/06/27/HandlingCommaSeparatedValuesInsideAMapPartII.aspx
Fifth option
The fifth and last option i want to mention is, that with the new SQL Server
LOB adapter from Adapter Pack 2.0, it appears that you can do it like the customer
wanted to do it in the first place with sending one XML to SQL Server and getting
an accumulated response back from SQL Server based on several calls to a stored procedure.
I haven’t had time to test this, but look out for another blog post about this 🙂
Hope this helps someone.
—
eliasen