The argument 1 of the XML data type method "value" must be a string literal

Question

How to change my T-SQL query so that this error doesn't occur:

'The argument 1 of the 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 1

You must use sql variable implicitly:

Declare @Count Int = 1
While(@count <= @j)
Begin
insert into mytable
([Word])
Select ([XmlColumn].value('(/word[sql:variable("@Count")]/@Entry)[1]','nvarchar(max)'))
    from OtherTable WHERE ID=2

Solution 2

You can remove the while loop and do the insert in one go using nodes to shred the XML.

insert into mytable([Word])
select N.value('@Entry', 'nvarchar(max)')
from OtherTable
  cross apply XmlColumn.nodes('word') as T(N)
where ID = 2

If @j is there to limit the number of rows you want to insert to mytable you can use this instead.

insert into mytable([Word])
select ID
from
  (
    select N.value('@Entry', 'nvarchar(max)') as ID, 
           row_number() over(order by T.N) as rn
    from OtherTable
      cross apply XmlColumn.nodes('word') as T(N)
    where ID = 2
  ) T
where rn <= @j

If you for some reason really want to use the loop then you can do like this instead.

while @Count <= @j
begin
  insert into mytable([Word])
  select XMLColumn.value('(/word[sql:variable("@Count")]/@Entry)[1]', 'nvarchar(max)')
  from OtherTable
  where ID = 2

Comments

[Mikael Eriksson]

Your xpath expression does not resolve to a single value. Well.. actually it does but the compiler does not know that.

[ARZ]

this problem was in original query. anyway, the ans has been updated.

[Mikael Eriksson]

No it was not. Using [1] or any other number will work just fine. It failed because you introduced sql:variable.

[ivan_pozdeev]

sql:variable only works inside an XPath expression. I can't e.g. insert an entire XML DML incantation or declare statements from a variable.

[elle0087]

hi, so is not possible define dynamically the output type, like varchar(value) or decimal(x,y)??