Sql删除xml的多个node

[QuickPai]

我描述一下问题，我现在sql存储过程里面有一个XML类型的参数，现在我想删除这个参数里面的几个节点，举例如下：
XML
<Root>
<Data>
<Id>1</Id>
<Name>Kevin</Name>
<Des>Des1</Des>
</Data>
<Data>
<Id>2</Id>
<Name>Alex</Name>
<Des>Des2</Des>
</Data>
<Data>
<Id>3</Id>
<Name>Amy</Name>
<Des>Des3</Des>
</Data>
</Root>

现在我想删除：(Id = 2 AND Name = 'Alex') OR (Id = 3 AND Name = 'Amy') 的这2条记录，输出结果XML为：
<Root>
<Data>
<Id>1</Id>
<Name>Kevin</Name>
<Des>Des1</Des>
</Data>
</Root>

请问该如何写这个Sql操作XMl的脚本？ 谢谢！

[發糞塗牆]

貌似不能一次性删除多个相同级别的节点，要循环删除，删除操作可以看：http://www.microsoft.com/china/msdn/library/data/sqlserver/XMLDML.mspx?mfr=true

[QuickPai]

引用 1 楼 DBA_Huangzj 的回复:

貌似不能一次性删除多个相同级别的节点，要循环删除，删除操作可以看：http://www.microsoft.com/china/msdn/library/data/sqlserver/XMLDML.mspx?mfr=true

如果要循环删除的话估计要用到游标了，还有其他的办法吗？

[發糞塗牆]

目前没见过批量的，所以我也没办法

[唐诗三百首]

declare @x xml

select @x='
<Root>
  <Data>
    <Id>1</Id>
    <Name>Kevin</Name>
    <Des>Des1</Des>
  </Data>
  <Data>
    <Id>2</Id>
    <Name>Alex</Name>
    <Des>Des2</Des>
  </Data>
  <Data>
    <Id>3</Id>
    <Name>Amy</Name>
    <Des>Des3</Des>
  </Data>
</Root>'

set @x.modify('delete /Root/*[3]')
set @x.modify('delete /Root/*[2]')

select @x 'newxml'

/*
newxml
------------------------------
<Root>
  <Data>
    <Id>1</Id>
    <Name>Kevin</Name>
    <Des>Des1</Des>
  </Data>
</Root>

(1 row(s) affected)
*/

[唐诗三百首]

方法2,

declare @x xml

select @x='
<Root>
  <Data>
    <Id>1</Id>
    <Name>Kevin</Name>
    <Des>Des1</Des>
  </Data>
  <Data>
    <Id>2</Id>
    <Name>Alex</Name>
    <Des>Des2</Des>
  </Data>
  <Data>
    <Id>3</Id>
    <Name>Amy</Name>
    <Des>Des3</Des>
  </Data>
</Root>'

-- 转为临时表
if object_id('tempdb..#t') is not null
 drop table #t

select o.value('Id[1]','int') 'Id',
       o.value('Name[1]','varchar(10)') 'Name',
       o.value('Des[1]','varchar(10)') 'Des'
into #t
from (select @x 'x') t
cross apply x.nodes('/Root/Data') x(o)

-- 按条件删除
delete from #t
 where (Id=2 and Name='Alex') or (Id=3 and Name='Amy')

-- 结果保存回@x
select @x=
(select * from #t for xml raw('Data'),root('Root'),elements)

-- 显示结果
select @x 'newxml'

/*
newxml
-----------------------------
<Root>
  <Data>
    <Id>1</Id>
    <Name>Kevin</Name>
    <Des>Des1</Des>
  </Data>
</Root>

(1 row(s) affected)
*/

[QuickPai]

谢谢大家，我直接处理xml也得到了答案，如下：

declare @xml xml
    set @xml = '<Root><Header>123</Header><Data><Id>1</Id><Name>Kevin</Name><Des>Des1</Des></Data><Data><Id>2</Id><Name>Alex</Name><Des>Des2</Des></Data><Data><Id>3</Id><Name>Amy</Name><Des>Des3</Des></Data><Tail>456</Tail></Root>'
    --set @xml.modify('delete /Root/Data[(Id[.=1] and Name[.="Kevin"]) or (Id[.=2] and Name[.="Alex"])]')
    select @xml
    declare @parameter XML
    set @parameter = (SELECT Node.value('(Id)[1]', 'Int') AS Id,
                            Node.value('(Name)[1]', 'Varchar(10)') AS Name
                    FROM @xml.nodes('Root/Data') TempXML(Node)
                    WHERE Node.value('(Id)[1]', 'Int') != 3
                    for xml path('Root'), TYPE)
    --select @parameter
    declare @sql nvarchar(max)
    set @sql = convert(nvarchar(max), 
     @parameter.query('for $i in (/Root) return concat("(Id[.=", 
                                                            string($i/Id[1]),
                                                            "] and Name[.=""", 
                                                            string($i/Name[1]),
                                                            """]) or")')
                                                            )
    set @sql = '['+substring(@sql,0,len(@sql)-2)+']'
    set @sql = 'set @xml.modify(''delete /Root/Data'+@sql+''')'
    select @sql
    exec sp_executesql @sql, N'@xml xml output', @xml output
    select @xml

参考：http://stackoverflow.com/questions/22253814/how-to-using-for-loop-using-xquery-to-delete-xml-nodes/22264365#22264365