Deleting Multiple Nodes in Single XQuery for SQL Server

Question

I have:

1. a table with an xml type column (list of IDs)
2. an xml type parameter (also list of IDs)

What is the best way to remove nodes from the column that match the nodes in the parameter, while leaving any unmatched nodes untouched?

e.g.

declare @table table (
    [column] xml
)

insert @table ([column]) values ('<r><i>1</i><i>2</i><i>3</i></r>')

declare @parameter xml
set @parameter = '<r><i>1</i><i>2</i></r>'

-- this is the problem
update @table set [column].modify('delete (//i *where text() matches @parameter*)')

The MSDN documentation indicates it should be possible (in Introduction to XQuery in SQL Server 2005):

This stored procedure can easily be modified to accept an XML fragment which contains one or more skill elements thereby allowing the user to delete multiple skill nodes with a single invocation of stored procedure.

Answer 1

You need something in the form:

[column].modify('delete (//i[.=(1, 2)])') -- like SQL IN
-- or
[column].modify('delete (//i[.=1 or .=2])')
-- or
[column].modify('delete (//i[.=1], //i[.=2])')
-- or
[column].modify('delete (//i[contains("|1|2|",concat("|",.,"|"))])')

XQuery doesn't support xml SQL types in SQL2005, and the modify method only accepts string literals (no variables allowed).

Here's an ugly hack w/ the contains function:

declare @table table ([column] xml)
insert @table ([column]) values ('<r><i>1</i><i>2</i><i>3</i></r>')

declare @parameter xml
set @parameter = '<r><i>1</i><i>2</i></r>'

-- build a pipe-delimited string
declare @in nvarchar(max)
set @in = convert(nvarchar(max),
    @parameter.query('for $i in (/r/i) return concat(string($i),"|")')
  )
set @in = '|'+replace(@in,'| ','|')

update @table set [column].modify ('
  delete (//i[contains(sql:variable("@in"),concat("|",.,"|"))])
  ')
select * from @table

Here's another w/ dynamic SQL:

-- replace table variable with temp table to get around variable scoping
if object_id('tempdb..#table') is not null drop table #table
create table #table ([column] xml)
insert #table ([column]) values ('<r><i>1</i><i>2</i><i>3</i></r>')

declare @parameter xml
set @parameter = '<r><i>1</i><i>2</i></r>'

-- we need dymamic SQL because the XML modify method only permits string literals
declare @sql nvarchar(max)
set @sql = convert(nvarchar(max), 
    @parameter.query('for $i in (/r/i) return concat(string($i),",")')
  )
set @sql = substring(@sql,1,len(@sql)-1)
set @sql = 'update #table set [column].modify(''delete (//i[.=('+@sql+')])'')'
print @sql
exec (@sql)

select * from #table

if you are updating an xml variable rather than a column, use sp_executesql and output parameters:

declare @xml xml
set @xml = '<r><i>1</i><i>2</i><i>3</i></r>'

declare @parameter xml
set @parameter = '<r><i>1</i><i>2</i></r>'

declare @sql nvarchar(max)
set @sql = convert(nvarchar(max), 
    @parameter.query('for $i in (/r/i) return concat(string($i),",")')
  )
set @sql = substring(@sql,1,len(@sql)-1)
set @sql = 'set @xml.modify(''delete (//i[.=('+@sql+')])'')'
exec sp_executesql @sql, N'@xml xml output', @xml output

select @xml

Alternate method using a cursor to iterate through delete values, probably less efficient due to multiple updates:

declare @table table ([column] xml)
insert @table ([column]) values ('<r><i>1</i><i>2</i><i>3</i></r>')

declare @parameter xml
set @parameter = '<r><i>1</i><i>2</i></r>'

/*
-- unfortunately, this doesn't work:
update t set [column].modify('delete (//i[.=sql:column("p.i")])')
from @table t, (
  select i.value('.', 'nvarchar')
  from @parameter.nodes('//i') a (i)
  ) p (i)
select * from @table
*/

-- so we have to use a cursor
declare @cursor cursor
set @cursor = cursor for
  select i.value('.', 'varchar') as i
  from @parameter.nodes('//i') a (i)

declare @i int
open @cursor
while 1=1 begin
  fetch next from @cursor into @i
  if @@fetch_status <> 0 break
  update @table set [column].modify('delete (//i[.=sql:variable("@i")])')
end

select * from @table

More information on the limitations of XML variables in SQL2005 here:

http://blogs.msdn.com/denisruc/archive/2006/05/17/600250.aspx

Does anyone have a better way?

Answer 2

While the delete is a little awkward to do this way, you can instead do an update to change the data, provided your data is simple (such as the example you gave). The following query will basically split the two XML strings into tables, join them, exclude the non-null (matching) values, and convert it back to XML:

UPDATE @table 
SET [column] = (
 SELECT p.i.value('.','int') AS c
 FROM [column].nodes('//i') AS p(i)
 OUTER APPLY (
  SELECT x.i.value('.','bigint') AS i
  FROM @parameter.nodes('//i') AS x(i)
  WHERE p.i.value('.','bigint') = x.i.value('.','int')
 ) a
 WHERE a.i IS NULL
 FOR XML PATH(''), TYPE
)