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?

Estonian

Answer 1

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

Septenary

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

Answer 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

Impanel

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

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()=... – Impanel