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