Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hi,

i have an xml as below

declare @xml xml = '<my><Fields><Fields_1>1</Fields_1><Fields_2>2</Fields_2><Fields_3>4</Fields_3> </Fields></my>'


I want to delete multiple nodes in a single sql statement.
i.e. I need a sql query to remove two nodes say Field_1 and Field_2 dynamically.

XML will be final as follows:-
'<my><Fields><Fields_3>4</Fields_3> </Fields></my>'


What I have tried:

I have found that it is achievable using

SET @xml.modify('
delete
(
/my/Fields/Fields_1,
/my/Fields/Fields_2
)
')



However, my requirement is that the nodes would be dynamically created. i.e,
say @node_1 = '
/my/Fields/Fields_1
'
@node_2 = '
/my/Fields/Fields_2
'
and need to execute the delete.

I want to create above query dynamically to delete multiple nodes or is there any other way to achieve it i.e. something like:-

<pre>SET @xml.modify('
delete
(
@node1,
@node2
)
')
Posted
Updated 12-Jul-18 1:33am
v3

1 solution

This is the 'delete' way:
SQL
SET @XML.modify('delete /my/Fields/Fields_1')

The reason is that modify can not get variables only literal...
You can create of course dynamic SQL...
sp_executesql (Transact-SQL) | Microsoft Docs[^]
 
Share this answer
 
v2
Comments
TarunShrivastav 12-Jul-18 7:56am    
Thanks,
Can you please give an example of how to create it dynamically.
TarunShrivastav 12-Jul-18 7:58am    
is there any other alternative of deleting multiple node without using modify so that we can use dynamic sql
Kornfeld Eliyahu Peter 12-Jul-18 8:05am    
The other way is to select only the nodes you do NOT want to delete, but obviously it is not dynamic either...
TarunShrivastav 12-Jul-18 8:14am    
but i think it is easy to retain structure when u use modify but is it possible to retain xml structure when we select node that we do not want to delete.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900