CODEWITHSUNDEEP
sql-serverxml
Nominalista
Nominalista

Reputation: 4840

Updating xml with variables in SQL Server

Let's say we have simple table T1 which has three columns:

CREATE TABLE T1(C1 INT PRIMARY KEY, C2 VARCHAR(20), C3 XML)

Now we create simple data:

INSERT INTO T1 VALUES(1, 'Test', '<Element></Element>')

Then I want to modify third column to achieve something like this:

<Element>Test</Element>

Which means, C2 is inserted into XML.

So I wanted to do that with variables:

DECLARE @test VARCHAR(20) = 'Example'
UPDATE
     T1
SET 
     @test = C2,
     C3.modify('
     replace value of
     (/Element/text())[1]
     with sql:variable("@test") ')

Unfortunately the result is:

<Element>Example</Element>

What I'm doing wrong?

Upvotes: 3

Views: 2164

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

You are modifying the underlying variable in the same statement where it is used; this doesn't work in SQL Server. Either:

Split variable assignment and usage into two separate statements:

DECLARE @test VARCHAR(20);

select @test = t.C2
from T1 t;

UPDATE t SET C3.modify('
  replace value of (/Element/text())[1] with sql:variable("@test")
  ')
from T1 t;

OR

Use the value of the column directly:

UPDATE t SET C3.modify('
  replace value of (/Element/text())[1] with sql:column("t.C2")
  ')
from T1 t;

Unless you have some complex logic behind the variable value calculation, the second option is preferred due to performance reasons - you touch the table only once, not twice. Also, the second variant is highly recommended if you need to update more than 1 row, each with different values.

Upvotes: 5

Related Questions