Hi Kelly,

You don't have to create a temporary table from XML. You can JOIN your SQL table to the OPENXML() directly. For example.

USE Northwind
CREATE PROCEDURE usp_ProductsSelect
 @ProductIDs text
AS
 DECLARE @hDoc int
 EXECUTE sp_xml_preparedocument @hDoc OUTPUT, @ProductIDs
 SELECT * 
  FROM Products pr
	JOIN OPENXML(@hDoc, 'Products/id') WITH (value int)Ids
		ON pr.Productid = ids.value 
 EXECUTE sp_xml_removedocument @hDoc 
GO

set @ProdListID = '<Products> 
<id value="7"/> 
<id value="12"/> 
<id value="55"/> 
<id value="72"/> 
</Products>'
execute usp_ProductsSelect @ProdListID

You can do record by record processing using SQL cursor. It's documented well in BOL. Ther's also way to do that w/o cursor as shown below.

CREATE PROCEDURE usp_ProductsSelect
 @ProductIDs text
AS
 DECLARE @hDoc int, @rc int, @i int, @id int
SET NOCOUNT ON
EXECUTE sp_xml_preparedocument @hDoc OUTPUT, @ProductIDs
IF object_id('tempdb..#temp01') IS NOT NULL
	DROP TABLE #temp01
CREATE TABLE #temp01
	( value int, rn int IDENTITY )
INSERT INTO #temp01 (value) 
 SELECT value FROM  OPENXML(@hDoc, 'Products/id') WITH (value int)Ids
SET @rc = @@ROWCOUNT
SET @i = 1
WHILE @i <= @rc
BEGIN
	SELECT @id = value FROM #temp01 WHERE rn = @i
	-- Now you've id, use it
	SELECT * FROM Products WHERE Productid = @id
	--
	SET @i = @i + 1
END
 EXECUTE sp_xml_removedocument @hDoc 
GO

>I have a procedure that receives an xml value for one of the parameters. I take the value and turn that into a table to perform a join with another table to get my result. What I have noticed is that when the passed value contains a bunch of numbers the performance goes down. How would I test to determine if it is faster to use a join on the created temporary table or go from the top to the bottom one record at a time and insert the returned values from that? With VFP I could use the SKIP to go from one record to the next but how would I mimick this in SQL Server?
>