如何在单条Update SQL语句中同时更新多个XML节点

错误原因 #

SQL Server 不支持在 UPDATE 语句的 SET 子句中对同一个 XML 列多次调用 .modify() 方法，同一列在单条 UPDATE 中只能被赋值一次。同时你的原代码中 ValueType 节点的 XPath 索引写错，XML 中仅存在1个 ValueType 节点，应该用[1]而非[2]，否则会匹配不到节点导致更新失败。

解决方案1：使用FLWOR表达式重构XML（推荐，单条UPDATE完成） #

通过XQuery的FLWOR语句遍历所有节点，匹配到目标节点时替换值，其余节点保持不变，一次性完成多节点更新：

DECLARE @Type nVarchar(10) = 'MS'
DECLARE @ValueType nVarchar(10) = 'OPT'
DECLARE @TransactionId bigint = 122344555

UPDATE 
   Table1
SET 
   Data = Data.query('
      for $node in /TransmissionData/*
      return
          if (local-name($node) = "CardType") then element CardType { sql:variable("@Type") }
          else if (local-name($node) = "ValueType") then element ValueType { sql:variable("@ValueType") }
          else if (local-name($node) = "TransactionDetails") then 
              element TransactionDetails {
                  for $tdNode in $node/*
                  return 
                      if (local-name($tdNode) = "TransactionId") then element TransactionId { sql:variable("@TransactionId") }
                      else $tdNode
              }
          else $node
   ')
WHERE
   RequestId = 2133831593

如果你的XML结构固定、需要修改的节点少，也可以直接写静态节点构造逻辑，执行效率更高：

DECLARE @Type nVarchar(10) = 'MS'
DECLARE @ValueType nVarchar(10) = 'OPT'
DECLARE @TransactionId bigint = 122344555

UPDATE 
   Table1
SET 
   Data = Data.query('
      element TransmissionData {
          /TransmissionData/CardHolderName,
          element CardType { sql:variable("@Type") },
          element TransactionDetails {
              element TransactionId { sql:variable("@TransactionId") }
          },
          element ValueType { sql:variable("@ValueType") }
      }
   ')
WHERE
   RequestId = 2133831593

解决方案2：先修改XML变量再回写 #

如果更新逻辑复杂，也可以先把目标行的XML读取到变量，多次调用.modify()修改完成后再回写表：

DECLARE @Type nVarchar(10) = 'MS'
DECLARE @ValueType nVarchar(10) = 'OPT'
DECLARE @TransactionId bigint = 122344555
DECLARE @TempXml XML

-- 读取目标行的XML到变量
SELECT @TempXml = Data FROM Table1 WHERE RequestId = 2133831593

-- 多次调用modify修改节点
SET @TempXml.modify('replace value of (/TransmissionData/CardType/text())[1] with sql:variable("@Type")')
SET @TempXml.modify('replace value of (/TransmissionData/ValueType/text())[1] with sql:variable("@ValueType")')
SET @TempXml.modify('replace value of (/TransmissionData/TransactionDetails/TransactionId/text())[1] with sql:variable("@TransactionId")')

-- 回写更新到表
UPDATE Table1 SET Data = @TempXml WHERE RequestId = 2133831593

内容的提问来源于stack exchange，提问作者BSSwathi