The argument 1 of the XML data type method "value" must be a string literal
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 IDfrom ( 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 ) Twhere rn <= @j
If you for some reason really want to use the loop then you can do like this instead.
while @Count <= @jbegin insert into mytable([Word]) select XMLColumn.value('(/word[sql:variable("@Count")]/@Entry)[1]', 'nvarchar(max)') from OtherTable where ID = 2