Удаление нескольких узлов в одном XQuery для SQL Server

Я имею:

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

Каков наилучший способ удалить узлы из столбца, которые соответствуют узлам в параметре, оставляя при этом любые несопоставленные узлы нетронутыми?

например

<code>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*)')
</code>

Документация MSDN указывает, что это должно быть возможно (вВведение в XQuery в 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.

Ответы на вопрос(2)

Решение Вопроса

вы можете вместо этого сделать обновление, чтобы изменить данные, при условии, что ваши данные просты (например, пример, который вы привели). Следующий запрос в основном разделит две строки XML на таблицы, объединит их, исключит ненулевые (совпадающие) значения и преобразует их обратно в 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
)

Вам нужно что-то в форме:

[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 не поддерживает XML-типы XML в SQL2005, а метод modify принимает только строковые литералы (переменные не допускаются).

Вот уродливый хак с функцией содержит:

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

Вот еще один с динамическим 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[.=('[email protected]+')])'')'
print @sql
exec (@sql)

select * from #table

если вы обновляете переменную xml, а не столбец, используйте sp_executesql и выходные параметры:

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[.=('[email protected]+')])'')'
exec sp_executesql @sql, N'@xml xml output', @xml output

select @xml

Альтернативный метод, использующий курсор для итерации по удаляемым значениям, возможно менее эффективный из-за многократных обновлений:

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

Больше информации об ограничениях переменных XML в SQL2005 здесь:

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

У кого-нибудь есть способ получше?

Ваш ответ на вопрос