Update multiple XML nodes in single Update SQL statement

Question

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.

It is a limitation of SQL Server XQuery. It allows just one update at a time. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Oct 12, 2021 at 21:34
Does this answer your question? xml-sql: update multiple nodes — Stu
– Stu, Commented Oct 12, 2021 at 21:48

Yitzhak Khabinsky · Accepted Answer · 2021-10-12 21:59:49Z

1

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;

edited Oct 12, 2021 at 21:59

answered Oct 12, 2021 at 21:52

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BSSwathi Over a year ago

Thank you so much Yitzhak Khabinsky - its worked fine.

Collectives™ on Stack Overflow

Update multiple XML nodes in single Update SQL statement

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related