Deleting Multiple Nodes In Single Xquery For Sql Server

I have: a table with an xml type column (list of IDs) 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 pa

Solution 1:

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@tableSET [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 ISNULLFOR XML PATH(''), TYPE
)

Solution 2:

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@tabletable ([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 stringdeclare@in nvarchar(max)
set@in=convert(nvarchar(max),
    @parameter.query('for $i in (/r/i) return concat(string($i),"|")')
  )
set@in='|'+replace(@in,'| ','|')

update@tableset [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') isnotnulldroptable #tablecreatetable #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 literalsdeclare@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 @sqlexec (@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@tabletable ([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 cursordeclare@cursorcursorset@cursor=cursorforselect i.value('.', 'varchar') as i
  from@parameter.nodes('//i') a (i)

declare@iintopen@cursor
while 1=1beginfetch next from@cursorinto@i
  if @@fetch_status <>0 break
  update@tableset [column].modify('delete (//i[.=sql:variable("@i")])')
endselect*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?