SQL Server xml.modify删除方法

时间:2021-12-07 09:10:22

I have a problem with removing an attribute from a node.

我从节点中删除属性时遇到问题。

Example:

例:

DECLARE @processID int
SET @processID = 8

DECLARE @xml XML
SET @xml = 
'<Process id="10" name="Test 1">
  <Shapes>
    <Shape id="1" name="Shape 1" subProcessID="8">
    </Shape>
    <Shape id="2" name="Shape 2" subProcessID="9">
    </Shape>
  </Shapes>
  <Lines />
</Process>'

SET @xml.modify('delete (/Process/Shapes/Shape/@subProcessID[/Process/Shapes/Shape/@subProcessID = sql:variable("@processID")])')
SELECT @xml

Gives the result:

给出结果:

<Process id="10" name="Test 1">
  <Shapes>
    <Shape id="1" name="Shape 1" />
    <Shape id="2" name="Shape 2" />
  </Shapes>
  <Lines />
</Process>

What I would like is:

我想要的是:

<Process id="10" name="Test 1">
  <Shapes>
    <Shape id="1" name="Shape 1" />
    <Shape id="2" name="Shape 2" subProcessID="9" />
  </Shapes>
  <Lines />
</Process>

What is the syntax to achieve this?

实现这个的语法是什么?

1 个解决方案

#1


0  

As the OP is gone but he left the solution in a comment let me add that as an answer:

由于OP已经消失,但他在评论中留下了解决方案,让我补充一下作为答案:

DECLARE @processID int
SET @processID = 8

DECLARE @xml XML
SET @xml = 
'<Process id="10" name="Test 1">
  <Shapes>
    <Shape id="1" name="Shape 1" subProcessID="8">
    </Shape>
    <Shape id="2" name="Shape 2" subProcessID="9">
    </Shape>
  </Shapes>
  <Lines />
</Process>'

SET @xml.modify('delete (/Process/Shapes/Shape[@subProcessID = sql:variable("@processID")]/@subProcessID)')
SELECT @xml

Here is a working sqlfiddle for that.

这是一个工作的sqlfiddle。

#1


0  

As the OP is gone but he left the solution in a comment let me add that as an answer:

由于OP已经消失,但他在评论中留下了解决方案,让我补充一下作为答案:

DECLARE @processID int
SET @processID = 8

DECLARE @xml XML
SET @xml = 
'<Process id="10" name="Test 1">
  <Shapes>
    <Shape id="1" name="Shape 1" subProcessID="8">
    </Shape>
    <Shape id="2" name="Shape 2" subProcessID="9">
    </Shape>
  </Shapes>
  <Lines />
</Process>'

SET @xml.modify('delete (/Process/Shapes/Shape[@subProcessID = sql:variable("@processID")]/@subProcessID)')
SELECT @xml

Here is a working sqlfiddle for that.

这是一个工作的sqlfiddle。