Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deleting Multiple Nodes in Single XQuery for SQL Server

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.

like image 482
Anton Avatar asked Jun 19 '09 22:06

Anton


2 Answers

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
)
like image 123
jvenema Avatar answered Oct 18 '22 03:10

jvenema


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?

like image 28
Peter Radocchia Avatar answered Oct 18 '22 02:10

Peter Radocchia