Apr 27, 2014

XQuery: The name supplied to sql:variable('') is not a valid SQL variable name. Variable names must start with the '@' symbol followed by at least one character.

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 9519, Level 16, State 1, Line 12
XQuery: The name supplied to sql:variable('$vcDbName') is not a valid SQL variable name. Variable names must start with the '@' symbol followed by at least one character.

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

Solution:  In sql server local variable name must start with @ symbol. And we must have to fist declare it. If you want to use system function then it must be valid system function. 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

No comments:

Post a Comment