Taking Advantage of Xml Data Type in Sql Server 2005
There are a ton of interesting things you can do with the new Xml data type in Sql Server 2005. The first quick thing that came to mind was to pass in an Xml document into a stored procedure to have many rows deleted at one time in a batch without having to use a loop with a transaction outside of Sql Server in .NET code. This could be done before in Sql Server 2000 by passing in an Xml document as a nvarchar then using OPENXML to parse out the values you need. I find the new way to be a bit cleaner. Here's a little t-sql script you could easily turn into a stored procedure to do multiple deletes at once.
DECLARE
@IDList xml SET
@IDList = '<IDList><Item ID="1" /><Item ID="2" /><Item ID="3" /></IDList>' DELETE
FROM
MyTable
WHERE
MyTableID IN
(
SELECT
IDList.Item.value('@ID', 'int') AS ID
FROM
@IDList.nodes('/IDList/Item') IDList (Item)
) In short, it's just saying, give me the nodes that match this XQuery, then gives the results an alias of IDList, saying that it has one field in it called Item and then calling the value method on it asking for the attribute called ID and making sure it's returned as an int. It then returns that result to the calling delete statement so any row with those IDs in your table will be deleted. Pretty simple and neat, but the xml data type starts to get really neat when you start storing metadata about each row. You can use the documented methods to pull out different fields in your result set from the xml field in your table so they act just like any other field inside of Sql Server.
I am by no means an expert at Sql Server 2005 so if there's a better way to do what I've demonstrated, please post a comment. The documentation seems to be a big lacking right now.