Search code examples
sqlxmlt-sqlsqlxml

XML data type method “value” must be a string literal

How to change my query so that this error doesn't happen:

XML data type method “value” must be a string literal

T-SQL code:

Declare @Count Int = 1 
While(@count <= @j) 
Begin 
insert into mytable 
([Word]) 
Select ([XmlColumn].value(N'word['+Cast(@Count as nvarchar(2))+']/@Entry','nvarchar(max)')) 
    from OtherTable WHERE ID=2
Solution

  • You cannot concatenate variables as strings in this way for the value method. You need to use sql:variable("@VariableName").

    So your example would be something like this:

    Declare @Count Int = 1 
While(@count <= @j) 
Begin 
insert into mytable 
([Word]) 

Select ([XmlColumn].value(N'/word[sql:variable("@Count")]/@Entry)[1]','nvarchar(max)'))
    from OtherTable WHERE ID=2