XML Data Type method “value” returns XQuery instead of the value

Question

Simplified example:

DECLARE @xml AS XML
SET @xml='
 <utmVisit>
    <utmSource>cpa1</utmSource>
 </utmVisit>'  

DECLARE @utmType NVARCHAR(255) = 'utmSource[1]'

SELECT c.value('utmSource[1]', 'nvarchar(255)')
    FROM @xml.nodes('//utmVisit') T(c) 
    
SELECT c.value('sql:variable("@utmType")', 'nvarchar(255)')
    FROM @xml.nodes('//utmVisit') T(c) 

The first select gives us "cpa1" which is the desired value.

The second one gives "utmSource[1]" which is the value of @utmType itself.

So I expect to get "cpa1" but got "utmSource[1]".

Can't I build XQuery dynamically?

Answer 1

As documented in the official Microsoft documentation:

value (XQuery, SQLType)

Arguments

XQuery Is the XQuery expression, a string literal, that retrieves data inside the XML instance. The XQuery must return at most one value. Otherwise, an error is returned.

So it is expecting a literal string.

Though you can use XPath predicate expression that could be dynamic. For example, use T-SQL variable, etc.

SQL

DECLARE @xml AS XML =
'<utmVisit>
    <utmSource>cpa1</utmSource>
 </utmVisit>';  

DECLARE @utmType NVARCHAR(255) = 'utmSource';

SELECT c.value('(utmSource/text())[1]', 'nvarchar(255)')
    FROM @xml.nodes('//utmVisit') T(c); 
    
SELECT c.value('(*[local-name(.) = sql:variable("@utmType")]/text())[1]', 'nvarchar(255)')
    FROM @xml.nodes('//utmVisit') T(c);