SQL Multiple Item Update Using XML

From time to time, I run into a situation where I need to update multiple rows on a table. I could make multiple requests to the database, but that is rather chatty. Alternatively, a comma delimited string of values could be parsed and used to get the update. Being a big fan of XQuery, I was able to pass in an xml document and load a table that could be used to join with the table to be updated. This should scale beautifully since it plays to SQL's strengths. I have looked for a way to get some metrics on the efficiency of this verses other options and could not find a way.

This is the function I used to generate XML string. I think there are ways that I can make the function a little tighter, but it does the job and is too inefficient.

?

XML Building Function:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

public static string BuildXmlString<t>(string xmlRootName, T[] values) {     StringBuilder xmlString = new StringBuilder();       xmlString.AppendFormat("<{0}>", xmlRootName);     int count = values.Length;     for (int idx = 0; idx < count; idx++)     {         xmlString.AppendFormat("<value>{0}</value>", values[idx]);     }     xmlString.AppendFormat("</{0}>", xmlRootName);       return xmlString.ToString(); }

Here is the SQL that uses the XML and updates a table with a resolved parameter value.

?

SQL Update Script:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

DECLARE @status varchar(50), @demoItemIDs xml  SET @status = ''     -- Load up the table variable with the parsed XML data  DECLARE @DemoItems TABLE (ID int)  INSERT INTO @DemoItems (ID) SELECT ParamValues.ID.value('.','int')  FROM @demoItemIDs.nodes('/demoItemIDs/value') as ParamValues(ID)    -- Resolve status name to ID  DECLARE @statusID int  SELECT TOP 1 @statusID = DemoStatusID FROM DemoStatuses ds WHERE ds.Name = @status    IF (@statusID > 0)  BEGIN   -- Update the payments with the new status   UPDATE [Demo].[dbo].[DemoItemApprovals]      SET DemoStatusID = @statusID    WHERE DemoItemID IN (     SELECT ID FROM @DemoItems d       INNER JOIN DemoItemApprovals da     ON    da.PaymentID = d.ID    )  END  ELSE  BEGIN   RAISERROR (    N'The status ''%s'' does not exist.' -- Message text.    , 10 -- Severity,    , 1 -- State,    , @status -- First argument.    );  END