Execute a DELETE command whilst iterating through an XML input parameter

Question

I have a stored procedure that receives 2 parameters.

@username VARCHAR(8), 
@xmlShiftDays XML

I want to delete multiple rows from the database while iterating through the XML.

I have managed to do something similar for an INSERT (see below)

INSERT INTO table(username, date)
   SELECT 
       username = @username, 
       CONVERT(DATETIME,shiftDate.date.value('.','VARCHAR(10)'),103)
   FROM 
       @xmlShiftDays.nodes('/shiftDates/date') as shiftDate(date)

This will successfully insert "x" amount of rows into my table.

I now want to re-engineer the query to DELETE "x" amount of rows. If anyone knows how or could point me in the right direction I would greatly appreciate it.

An example of what I want to achieve is:

DECLARE @username VARCHAR(8)
DECLARE @xmlShiftDays XML 

SET @xmlShiftDays = '<shiftDates><date>21/01/2012</date></shiftDates>'
SET @username = 'A0123456'

DELETE FROM table
WHERE username = @username
AND date = "<b>loop through the nodes in the XML string</b>"

Answer 1

Assuming you're using SQL Server 2008 (or newer) for this so I can use the DATE datatype (unfortunately, you didn't specify in your question which version of SQL Server you're using).....

I would strongly recommend you use a language-independent, regional-settings-independent date format in your XML - use the ISO-8601 format of YYYYMMDD for best results.

So try something like this:

DECLARE @xmlShiftDays XML 

SET @xmlShiftDays = '<shiftDates><date>20120122</date><date>20120227</date></shiftDates>'

;WITH DatesToDelete AS
(
    SELECT
        DeletionDate = DT.value('(.)[1]', 'date')
    FROM @XmlShiftDays.nodes('/shiftDates/date') AS SD(DT)
)
SELECT * FROM DatesToDelete

This should give you the two dates combined into XML string - right?

Now, you can use this to do the deletion from your table:

DECLARE @username VARCHAR(8)
DECLARE @xmlShiftDays XML 

SET @xmlShiftDays = '<shiftDates><date>20120122</date><date>20120227</date></shiftDates>'
SET @username = 'A0123456'

;WITH DatesToDelete AS
(
    SELECT
        DeletionDate = DT.value('(.)[1]', 'date')
    FROM @XmlShiftDays.nodes('/shiftDates/date') AS SD(DT)
)
DELETE FROM dbo.Table
WHERE username = @username
AND date IN (SELECT DeletionDate FROM DatesToDelete)

Does that work for you?