Strange behavior passing a variable in XML.value

  • 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

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

  • 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 1 month, 1 week ago by  PatLap.
  • Jep .... that's also part of life 🙂

    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

  • Actually I think I might have found a way to replicate the problem.  It looks like it fails if the database parametrization is set to forced.

    use master
    GO
    CREATE DATABASE PatTest
    GO
    USE PatTest
    GO
    -- This works correctly
    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
    WHERE 1 = 1;
    GO

    -- SET the parametrization to forced and try the above query again.... it fails
    ALTER DATABASE [PatTest] SET PARAMETERIZATION FORCED WITH NO_WAIT

    GO
    -- SET the parametrization back to simple and the above query will succeed
    ALTER DATABASE [PatTest] SET PARAMETERIZATION SIMPLE WITH NO_WAIT

    Anyone has any idea?

    Thank you

    Pat

     

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

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