Why does the xml value() method handle variables differently when executing a dynamically built string vs injection?

I’m perplexed as to why these 2 executions give different results.

Say I have the below XML:

<Main>
    <job id="1">
        <Action>true</Action>
    </job>
    <job id="2">
        <Action>false</Action>
    </job>
</Main>

The below SQL will give a null for actionvalue (I got the syntax from example A here):

exec sp_executesql
N'
select 
    j.value(''(job[@id="@jobid"])[1]/Action[1]'',''varchar(max)'') as actionvalue
from dbo.xmlstuff
    cross apply content.nodes(''/Main'') a(j)
N'@jobid varchar(1)',
@jobid = '1'

While the below gives the actual value (in this case true) (I got this syntax from here):

declare @sql nvarchar(500),
    @jobid varchar(1) = '1'
        
set @sql = 
'
select 
    j.value(''(job[@id="' + @jobid + '"])[1]/Action[1]'',''varchar(max)'') as actionvalue
from dbo.xmlstuff
    cross apply content.nodes(''/Main'') a(j)

exec sp_executesql @sql, N'@jobid varchar(1)', @jobid=@jobid;

Why is that? It obviously has something to do with how dynamically built string executions work vs how injection executions work, but the above 2 queries seem like they should accomplish the same thing.

I tried making jobid int and varchar with the same result. I also tried using more variables in the top query, and they work just fine. It’s just the piece within the square brackets after job that seems to have a problem with using variables.

>Solution :

They act differently because they aren’t the same. Your first attempt passes a variable’s name in quotes, meaning it is inferred as the literal value "@jobid" not the value of that parameter.

The latter attempt used injection, and thus injects a 1 into the string. You could remove the 2 latter parameters from the call to sys.sp_executesql in that attempt and it would still work.

You, however, don’t need dynamic SQL here. Instead tell XQuery that you are passing it a variable and the execution works:

declare @xml xml = '<Main>
    <job id="1">
        <Action>true</Action>
    </job>
    <job id="2">
        <Action>false</Action>
    </job>
</Main>';

declare @jobid char(1) = '1';

select 
    j.value('(job[@id=sql:variable("@jobid")])[1]/Action[1]','varchar(max)') as actionvalue
from (values(@xml)) X (content)
    cross apply content.nodes('/Main') a(j);

db<>fiddle