用xml到sql按条件更新所有节点

2021-10-01 00:00:00 xml sql-server

我有一个存储的 SQL 过程，它已经声明了具有这种结构的 xml 变量

I have a stored SQL procedure, which has declared xml variable with this kind of structure

<a>
  <b>4</b>
  <b>18</b>
  <b>2</b>
</a>

我需要为这个 XML 数据做一些类似 UPDATE a SET b=b-1 WHERE b>@MyIntVariable 的事情.在 MS Transact SQL 中执行此操作的最佳方法是什么?

I need to do something like UPDATE a SET b=b-1 WHERE b>@MyIntVariable for this XML data. What is the best way to do it in MS Transact SQL?

推荐答案

modify 函数最适合处理 xml 数据.请参阅 http://msdn.microsoft.com/en-us/library/ms190675(v=sql.105).aspx.

The modify function would be the most appropriate for manipulating xml data. See http://msdn.microsoft.com/en-us/library/ms190675(v=sql.105).aspx.

DECLARE @NUM INT = 10

DECLARE @xml XML = N'
<a>
  <b>4</b>
  <b>18</b>
  <b>2</b>
</a>
';
SELECT @XML;

DECLARE @COUNT INT
SET @COUNT = @XML.value ('count(/a/b)', 'int');

WHILE @COUNT > 0
BEGIN
    SET @XML.modify('replace value of (/a/b[sql:variable("@COUNT")]/text())[1] with 
    (
    if ((/a/b[sql:variable("@COUNT")])[1] > sql:variable("@NUM")) then
         (/a/b[sql:variable("@COUNT")])[1] - 1
       else
         (/a/b[sql:variable("@COUNT")])[1] cast as xs:double ?
    )
')
    SET @COUNT = @COUNT - 1;
END

SELECT @XML

相关文章