Strange behavior passing a variable in XML.value

Question

Strange behavior passing a variable in XML.value

PatLap

Mr or Mrs. 500

Points: 543
More actions
June 12, 2024 at 8:19 pm

#4428096

Hi, anyone would know why as soon as I add a WHERE statement (even WHERE 1 = 1) I would get an error?
Declare @Language nvarchar(10) = 'en'
DROP TABLE IF EXISTS #Temp
CREATE TABLE #Temp (TestName varchar(100),TestDescription varchar(max))
INSERT INTO #Temp values ('Test1','<multilanguage><text language=''en''>English Description</text><text language=''fr''>French Description</text></multilanguage>')
SELECT TestName,
CAST(TestDescription AS XML).value(N'(/multilanguage/text[@language=sql:variable("@Language")]/text())[1]', 'nvarchar(max)') AS [TestDescription]
FROM #Temp
That works but simply add any kind of WHERE statement
ex: SELECT TestName,
CAST(TestDescription AS XML).value(N'(/multilanguage/text[@language=sql:variable("@Language")]/text())[1]', 'nvarchar(max)') AS [TestDescription]
FROM #Temp
WHERE 1 = 1
and I get
Msg 9501, Level 16, State 2, Line 18
XQuery: Unable to resolve sql:variable('@Language'). The variable must be declared as a scalar TSQL variable.
If I replace the sql:variable("@Language") by "en" it will work with the WHERE statement
ex: SELECT TestName,
CAST(TestDescription AS XML).value(N'(/multilanguage/text[@language="en"]/text())[1]', 'nvarchar(max)') AS [TestDescription]
FROM #Temp
WHERE 1 = 1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

I don't see that. If I run the following, it executes just fine.

DECLARE @Language NVARCHAR(10) = N'en';

DROP TABLE IF EXISTS #Temp;

CREATE TABLE #Temp
(
    TestName VARCHAR(100)
   ,TestDescription VARCHAR(MAX)
);

INSERT INTO #Temp
VALUES
('Test1'
,'<multilanguage><text language=''en''>English Description</text><text language=''fr''>French Description</text></multilanguage>');

SELECT TestName
      ,TestDescription = CAST (TestDescription AS XML).value (
                                                                 N'(/multilanguage/text[@language=sql:variable("@Language")]/text())[1]'
                                                                ,'nvarchar(max)'
                                                             )
FROM #Temp
WHERE 1 = 1;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 2

I copy-pasted your text and commented all but the ddl and the queries.

Works just file.

Off course, in SSMS you need to keep the consequences of any "GO" line in mind ( and re-declare all variables you need )

2024-06-13 13_35_56-Window

DECLARE @Language NVARCHAR(10) = 'en';

DROP TABLE IF EXISTS #Temp;

CREATE TABLE #Temp
             (TestName        VARCHAR(100)
            , TestDescription VARCHAR(MAX)
             );

INSERT INTO #Temp
VALUES
       ( 'Test1', '<multilanguage><text language=''en''>English Description</text><text language=''fr''>French Description</text></multilanguage>'
       );
GO

DECLARE @Language NVARCHAR(10) = 'en';

SELECT TestName
     , CAST(TestDescription AS XML).value
       ( N'(/multilanguage/text[@language=sql:variable("@Language")]/text())[1]', 'nvarchar(max)'
       ) AS [TestDescription]
FROM   #Temp;

/*
That works but simply add any kind of WHERE statement

ex:*/

GO

DECLARE @Language NVARCHAR(10) = 'en';

SELECT TestName
     , CAST(TestDescription AS XML).value
       ( N'(/multilanguage/text[@language=sql:variable("@Language")]/text())[1]', 'nvarchar(max)'
       ) AS [TestDescription]
FROM   #Temp
WHERE  1 = 1;

/*
and I get

Msg 9501, Level 16, State 2, Line 18

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

If I replace the sql:variable("@Language") by "en" it will work with the WHERE statement

ex: */

GO

DECLARE @Language NVARCHAR(10) = 'en';

SELECT TestName
     , CAST(TestDescription AS XML).value
       ( N'(/multilanguage/text[@language="en"]/text())[1]', 'nvarchar(max)'
       ) AS [TestDescription]
FROM   #Temp
WHERE  1 = 1;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

PatLap Mr or Mrs. 500 Points: 543 More actions · Answer 3

Thank you both.

After seeing your replies I got the feeling it might be SSMS related. I started another instance of SSMS and as suspected... it worked.

I should have checked that before posting but ?#$& it can be frustrated sometime the way SSMS behaves. I had even traced the query and it was flagging it as an error.

Again thank you for your time

Pat

This reply was modified 6 months, 2 weeks ago by PatLap.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

Jep .... that's also part of life 🙂