22,300
社区成员




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