Naomi (and Sergey)

Regarding the XML approach for variable lists...there are new XML capabilities in SQL 2005 that eliminate the need for sp_xml_preparedocument and OPENXML. You can basically "shred" the XML using new XQUERY capabilities to create a reusable table-valued UDF. (I covered this in a prior CoDe article):

CREATE FUNCTION [dbo].[XML2TableVar] 
(  @XMLString XML) 
RETURNS 
   @tPKList TABLE  ( PKValue int) 

AS 
BEGIN 
            INSERT INTO @tPKList 
              SELECT Tbl.Col.value('.','int') 
                  FROM   @XMLString.nodes('//PKValue') Tbl( Col ) 

   RETURN 
END 

And then use it like this:

DECLARE @XMLString XML 
SET @XMLString ='<Customers> 
                 <Customer> 
                      <PKValue>2</PKValue> 
                      <PKValue>5</PKValue> 
                   </Customer> 
                  </Customers>' 


-- Show the contents 
SELECT  * FROM [dbo].[XML2TableVar] (@XMLString) CustList 

-- Perform a JOIN using the UDF 
SELECT Customers.* FROM Customers 
                        JOIN [dbo].[XML2TableVar] (@XMLString) CustList 
                                    ON Customers.CustomerPK = CustList.PKValue

Obviously, you'd need to modify this to support character columns, but you get the idea...


Kevin







Technical excellence is not a personal contest. Technical excellence is not a personal contest.