如何从SQL Server中的XML中删除多个节点 [英] How to delete multiple nodes from an XML in SQL server

问题描述

i有xml如下

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

我想在一个sql语句中删除多个节点。

即我需要一个sql查询来动态删除两个节点，比如Field_1和Field_2。



XML将是最终的，如下所示： -

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

但是，我的要求是动态创建节点。即，

说@ node_1 ='

However, my requirement is that the nodes would be dynamically created. i.e,
say @node_1 = '

/my/Fields/Fields_1

'

@ node_2 ='

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

推荐答案

这是'删除'的方式：

This is the 'delete' way:

SET @XML.modify('delete /my/Fields/Fields_1')

原因是 modify 无法仅获取文字的变量。 ..

您可以创建当然动态SQL ...

sp_executesql（Transact-SQL）| Microsoft Docs [ ^ ]

The reason is that modify can not get variables only literal...
You can create of course dynamic SQL...
sp_executesql (Transact-SQL) | Microsoft Docs[^]

这篇关于如何从SQL Server中的XML中删除多个节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！