Retrieving multiple item names based on lots of item numbers

by eliasen 18. September 2009 19:47

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

Tags:

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

About the author

Jan Eliasen is 37 years old, divorced and has 2 sons, Andreas (July 2004) and Emil (July 2006).

Jan has a masters degree in computer science and is currently employed at Logica Denmark as an IT architect.

Jan is a 6 times Microsoft MVP in BizTalk Server (not currently an MVP) and proud co-author of the BizTalk 2010 Unleashed book.

BizTalk Server 2010 Unleashed


Buy from Amazon

Microsoft MVP


6 times: July 2004, July 2008, July 2009, July 2010, July 2011, and July 2012. Not currently an MVP.

MCTS

Image to show

Month List

Page List