时间戳

首页 > 解决方案 > 在单个 Update SQL 语句中更新多个 XML 节点

sql - 在单个 Update SQL 语句中更新多个 XML 节点

问题描述

我想在单个更新查询中更新多个 XML 节点

XML:

<TransmissionData>
  <HolderName>Tony Chase</CardHolderName>
  <Type>VS</CardType>
   <TransactionDetails>
      <TransactionId />
   </TransactionDetails>
  <ValueType>CAPT</ValueType>
</TransmissionData>

这是我尝试过的：

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

UPDATE 
   Table1
SET 
   Data.modify('replace value of (/TransmissionData/CardType/text())[1] with sql:variable("@Type")'),
   Data.modify('replace value of (/TransmissionData/ValueType/text())[2] with sql:variable("@ValueType")'),
   Data.modify('replace value of (/TransmissionData/TransactionDetails/TransactionId/text())[1] with sql:variable("@TransactionId")')
WHERE
   RequestId = 2133831593

它仅适用于单个更新，如果我使用多个像 ValueType 和 TransactionId 这样的更新，它会显示错误。请帮助我 - 如何更新这个？

消息 264，级别 16，状态 1，第 7 行
在 SET 子句或 INSERT 的列列表中多次指定列名称“TransmissionData”。在同一个子句中，一列不能分配多个值。修改子句以确保一列只更新一次。如果此语句更新或将列插入到视图中，列别名可以隐藏代码中的重复项。

标签： sqlsql-server

解决方案

请尝试以下解决方案。

它使用 XQuery 及其 FLWOR 表达式。

它将更新有问题的 XML 元素，而其他所有内容都保持不变。

我必须修复输入 XML 以使其格式正确。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<TransmissionData>
  <CardHolderName>Tony Chase</CardHolderName>
  <CardType>VS</CardType>
   <TransactionDetails>
      <TransactionId />
   </TransactionDetails>
  <ValueType>CAPT</ValueType>
</TransmissionData>');
-- DDL and sample data population, end

DECLARE @Type NVARCHAR(10) = 'MS'
    , @ValueType NVARCHAR(10) = 'OPT'
    , @TransactionId BIGINT = 122344555;

UPDATE @tbl
SET xmldata = xmldata.query('<TransmissionData>
{
for $x in /TransmissionData/*
return if (local-name($x)="CardType") then 
        <CardType>{sql:variable("@Type")}</CardType>
    else if (local-name($x)="ValueType") then
        <ValueType>{sql:variable("@ValueType")}</ValueType>
    else if (local-name($x)="TransactionDetails") then
        <TransactionDetails>
            <TransactionId>{sql:variable("@TransactionId")}</TransactionId>
        </TransactionDetails>
    else $x
}
</TransmissionData>');

-- test
SELECT * FROM @tbl;

推荐阅读