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);