I want to update multiple XML nodes in single Update query

XML:

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

This is what I have tried:

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

It works only for single Update, if I use more then one like ValueType and TransactionId, it shows an error. Please help me - how to update this?

Msg 264, Level 16, State 1, Line 7
The column name 'TransmissionData' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

CodePudding user response：

Please try the following solution.

It is using XQuery and its FLWOR expression.

It will update XML elements in question, leaving everything else intact.

I had to fix the input XML to make it well-formed.

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;