If you run this script in SQL Server 2005:
create table #xmltemp
(
id int,
data xml null
)
insert into #xmltemp
select 1, ''
update #xmltemp
set data.modify('insert <a id="1" /> into /')
update #xmltemp
set data.modify('insert <a id="2" /> into /')
update #xmltemp
set data.modify('insert <a id="3" /> into /')
update #xmltemp
set data.modify('insert <a id="4" /> into /')
select * from #xmltemp
update x
set data.modify('delete //a[@id=sql:column("t.xmlid")]')
from #xmltemp x
inner join (
select 1 as id, 1 as xmlid union
select 1 as id, 2 as xmlid union
select 1 as id, 3 as xmlid
) t on t.id = x.id
select * from #xmltemp
drop table #xmltemp
You will get the following output:
id data
1 <a id="1" /><a id="2" /><a id="3" /><a id="4" />
id data
1 <a id="2" /><a id="3" /><a id="4" />
I would expect it to delete all three nodes rather than just the first, making the second select return:
id data
1 <a id="4" />
Is there a way to delete multiple nodes in a single query? Specifically, I want to delete all nodes that match criteria from a column in another table (in this example t is created on the fly but it could just as easily have been an actual table).
You can make xmlid from the joined query into a comma separated string and use that string in the predicate.