Modify sql server xml element name variable in xquery

Question

I need the name of the element is also a variable (is a parameter of the procedure)

Instead of this, which works fine:

DECLARE @VAR VARCHAR(5)
SET @VAR = 'false'
UPDATE CURVES
  SET CURVEENTITY.modify(
    'replace value of (/ElementName/text())[1] with  sql:variable("@VAR")') 
  WHERE ID = 3

But I would like something like this:

DECLARE @VAR VARCHAR(5)
DECLARE @VAR2 VARCHAR(20)
SET @VAR = 'false'
SET @VAR2 = 'ElementName'
UPDATE CURVES
  SET CURVEENTITY.modify(
    'replace value of (/sql:variable("@VAR2")/text())[1] with sql:variable("@VAR")') 
  WHERE ID = 3

But it does not work! How can I do this?

Mikael Eriksson · Accepted Answer · 2014-01-09 07:28:55Z

7

You can use local-name() in a predicate to find the node you want to modify.

declare @var2 varchar(50) = 'ElementName'
declare @var varchar(50) = 'false'

update CURVES
set CURVEENTITY.modify('replace value of ((*[local-name() = sql:variable("@var2")]/text())[1]) 
                        with sql:variable("@var")')
where ID = 3

answered Jan 9, 2014 at 7:28

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

1 Comment

PAULDAWG Over a year ago

I had to add // in front of the * before [local-name() to get it to update

StuartLC · Accepted Answer · 2015-01-28 05:35:53Z

2

AFAIK you can't dynamically compose the path dynamically using a /sql:variable in xquery - you can build the path as a string and then use dynamic sql to execute it (in which case you may as well substitute both sql:variables).

e.g.

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'UPDATE CURVES
            SET CURVEENTITY.modify(''replace value of (/' + @var2 
                     + '/text())[1] with "' + @var + '"'') 
            WHERE ID = 3';
exec sp_executesql @sql;

SQL Fiddle here

edited Jan 28, 2015 at 5:35

answered Jan 9, 2014 at 5:45

StuartLC

108k18 gold badges225 silver badges294 bronze badges

2 Comments

evbell Over a year ago

thank you! I suspect that the solution of Mikael Eriksson has better performance if I'm not mistaken?

StuartLC Over a year ago

Irrespective of performance, the local-name() approach is less smelly than dynamic sql, so @Mikael's answer is cleaner to maintain (+1). Note however that local-name() may match elements with the same name in different namespaces, in which case you would also need to qualify the element further via local-name() = ... and namespace-uri()=...

Collectives™ on Stack Overflow

Modify sql server xml element name variable in xquery

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related