Using sql:column function in xpath in values method

I have two tables:

One called @settings with xml values
Another called @nodesToFind with a list of nodes to extract from the xml values in the first table

I want to get a list of the values for each NodePath for each RowId.

This query uses the sql:column function in the xpath of the values method on the Settings column but it returns the NodePath itself instead of the value:

declare @settings table (RowId int identity, Settings xml)
insert @settings (Settings) values ('<settings><Settings1><Settings1a><Setting1a1>1-1a1</Setting1a1></Settings1a><Setting1b>1-1b</Setting1b><Setting1c>1-1c</Setting1c></Settings1><Settings2><Setting2a>1-2a</Setting2a></Settings2></settings>')
insert @settings (Settings) values ('<settings><Settings1><Settings1a><Setting1a1>2-1a1</Setting1a1></Settings1a><Setting1b>2-1b</Setting1b><Setting1c>2-1c</Setting1c></Settings1><Settings2><Setting2a>2-2a</Setting2a></Settings2></settings>')
insert @settings (Settings) values ('<settings><Settings1><Settings1a><Setting1a1>3-1a1</Setting1a1></Settings1a><Setting1b>3-1b</Setting1b><Setting1c>3-1c</Setting1c></Settings1><Settings2><Setting2a>3-2a</Setting2a></Settings2></settings>')

declare @nodesToFind table (NodePath varchar(max))
insert @nodesToFind (NodePath) values ('/Settings/Settings1/Settings1a/Setting1a1')
insert @nodesToFind (NodePath) values ('/Settings/Settings1/Setting1b')
insert @nodesToFind (NodePath) values ('/Settings/Settings1/Setting1c')
insert @nodesToFind (NodePath) values ('/Settings/Settings2/Setting2a')

select
    S.RowId, 
    NTF.NodePath,
    S.Settings.value('(sql:column("NodePath"))[1]', 'varchar(max)')
from @settings S
cross apply @nodesToFind NTF

The result is this:

RowId  NodePath                                   Value
-----  -----------------------------------------  -----------------------------------------
1      /Settings/Settings1/Settings1a/Setting1a1  /Settings/Settings1/Settings1a/Setting1a1
2      /Settings/Settings1/Settings1a/Setting1a1  /Settings/Settings1/Settings1a/Setting1a1
3      /Settings/Settings1/Settings1a/Setting1a1  /Settings/Settings1/Settings1a/Setting1a1
1      /Settings/Settings1/Setting1b              /Settings/Settings1/Setting1b
2      /Settings/Settings1/Setting1b              /Settings/Settings1/Setting1b
3      /Settings/Settings1/Setting1b              /Settings/Settings1/Setting1b
1      /Settings/Settings1/Setting1c              /Settings/Settings1/Setting1c
2      /Settings/Settings1/Setting1c              /Settings/Settings1/Setting1c
3      /Settings/Settings1/Setting1c              /Settings/Settings1/Setting1c
1      /Settings/Settings2/Setting2a              /Settings/Settings2/Setting2a
2      /Settings/Settings2/Setting2a              /Settings/Settings2/Setting2a
3      /Settings/Settings2/Setting2a              /Settings/Settings2/Setting2a

What is wrong with the S.Settings.value('(sql:column("NodePath"))[1]', 'varchar(max)') line?

Solution

The XQuery you are trying to use is effectively being run dynaimcally. Unfortunately, you cannot use dynamic XQuery in SQL Server. Each XQuery must be static.

What you could do in your specific situation, is to break up each node predicate into separate columns. Then in the XQuery you can descend to the relevant node by checking each column. For example:

declare @settings table (RowId int identity, Settings xml)
insert @settings (Settings) values ('<settings><Settings1><Settings1a><Setting1a1>1-1a1</Setting1a1></Settings1a><Setting1b>1-1b</Setting1b><Setting1c>1-1c</Setting1c></Settings1><Settings2><Setting2a>1-2a</Setting2a></Settings2></settings>')
insert @settings (Settings) values ('<settings><Settings1><Settings1a><Setting1a1>2-1a1</Setting1a1></Settings1a><Setting1b>2-1b</Setting1b><Setting1c>2-1c</Setting1c></Settings1><Settings2><Setting2a>2-2a</Setting2a></Settings2></settings>')
insert @settings (Settings) values ('<settings><Settings1><Settings1a><Setting1a1>3-1a1</Setting1a1></Settings1a><Setting1b>3-1b</Setting1b><Setting1c>3-1c</Setting1c></Settings1><Settings2><Setting2a>3-2a</Setting2a></Settings2></settings>')

declare @nodesToFind table (NodePath1 nvarchar(max), NodePath2 nvarchar(max), NodePath3 nvarchar(max), NodePath4 nvarchar(max))
insert @nodesToFind (NodePath1, NodePath2, NodePath3, NodePath4) values ('settings','Settings1','Settings1a','Setting1a1')
insert @nodesToFind (NodePath1, NodePath2, NodePath3, NodePath4) values ('settings','Settings1','Setting1b',null)
insert @nodesToFind (NodePath1, NodePath2, NodePath3, NodePath4) values ('settings','Settings1','Setting1c',null)
insert @nodesToFind (NodePath1, NodePath2, NodePath3, NodePath4) values ('settings','Settings2','Setting2a',null)

select
    S.RowId, 
    NTF.*,
    S.Settings.value('((
        for $i1 in *[local-name() = sql:column("NodePath1")]
        return 
            if (empty(sql:column("NodePath2")))
            then $i1
            else for $i2 in ($i1/*[local-name() = sql:column("NodePath2")])
                 return
                     if (empty(sql:column("NodePath3")))
                     then $i2
                     else for $i3 in $i2/*[local-name() = sql:column("NodePath3")]
                          return
                              if (empty(sql:column("NodePath4")))
                              then $i3
                              else $i3/*[local-name() = sql:column("NodePath4")]
    )/text())[1]', 'varchar(max)')
from @settings S
cross join @nodesToFind NTF

db<>fiddle

As you can see, it is made significantly more complex (and probably slow) by the fact that there are multiple possible node levels. If you can restrict it to only one level of node then you can remove the if else sections.