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) B

Solution 1:

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@CountInt=1 
While(@count<=@j) 
Begininsertinto mytable 
([Word]) 

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

Solution 2:

Thought I'd look for an approach to this that doesn't require the WHILE looping. The main problem is returning an item position along with the node, but I found a workaround in the last answer on this post: http://social.msdn.microsoft.com/Forums/nl/sqlxml/thread/46a7a90d-ebd6-47a9-ac56-c20b72925fb3

So the alternative approach would be:

insertinto mytable ([Word]) 
select word from (
    Select 
        words.value('@entry','nvarchar(max)') as word,
        words.value('1+count(for $a in . return $a/../*[. << $a])', 'int') as Pos   
    from
        OtherTable ot
        cross apply ot.XMLColumn.nodes('words/word') x(words)
) sub where Pos <=@j

Basically the inner query returns each word and its position, this can be filtered by the outer query.

For reference my definition of the OtherWords table was:

create table OtherTable (XMLColumn xml)
insert OtherTable (XMLColumn)
select '<words><wordentry="Wibble"/><wordentry="Hello"/><wordentry="World"/></words>'