Apr 27, 2014

XQuery: '' referenced by sql:variable() is not a valid system function name.

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("@@SERVERNAME()")')

We may get error message like:

Msg 9520, Level 16, State 1, Line 12
XQuery: '@@SERVERNAME()' referenced by sql:variable() is not a valid system function name.

Cause: The system function which you are using in the XQuery is invalid. In this example, sql server doesn't support any such system function like: @@SERVERNAME()

Solution:  Use valid system function. For example:

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("@@SERVERNAME")')

Same output:

RITESH-PC\SQLSERVER

No comments:

Post a Comment