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
)
')