XQuery: Unable to resolve sql:variable(''). The variable must be declared as a scalar TSQL variable

If we will execute following XQUERY script in the sql server:

DECLARE @xlMovie AS XML =

'

<Movies>

<tblMovie ntMovieID="1" vcMovieName="PAIN &amp; GAIN" />

<tblMovie ntMovieID="2" vcMovieName="OBLIVION" />

<tblMovie ntMovieID="3" vcMovieName="IRON MAN 3" />

<tblMovie ntMovieID="4" vcMovieName="EVIL DEAD" />

<tblMovie ntMovieID="5" vcMovieName="MAN OF STEEL" />

</Movies>

'

SELECT @xlMovie.query('sql:variable("@vcDbName")')

We may get error message like:

Msg 9501, Level 16, State 2, Line 12

XQuery: Unable to resolve sql:variable('@vcDbName'). The variable must be declared as a scalar TSQL variable.

Cause: The local variable which you are using in the XQuery is invalid. In this example there is any not local variable @vcDbName

Solution:  We must have to first declare the local variable before using in XQUERY. For example:

DECLARE @vcDbName AS SYSNAME = 'ExactHelp'

DECLARE @xlMovie AS XML =

'

<Movies>

<tblMovie ntMovieID="1" vcMovieName="PAIN &amp; GAIN" />

<tblMovie ntMovieID="2" vcMovieName="OBLIVION" />

<tblMovie ntMovieID="3" vcMovieName="IRON MAN 3" />

<tblMovie ntMovieID="4" vcMovieName="EVIL DEAD" />

<tblMovie ntMovieID="5" vcMovieName="MAN OF STEEL" />

</Movies>

'

SELECT @xlMovie.query('sql:variable("@vcDbName")')

Same output:

ExactHelp