nested xquery error in sql server 2005

HI There,

I'm trying to execute a nested xquery loop in the code below but I keep getting the error below.I have a xml file stored in a column with products and underneath those products I have multiple fields like actors etc I have a temporary table that i'm selecting from to get a barcode to enable the xquery loop to bring all of the actors for that product into my query so I can insert them into my table with concatentation.

I can't get the loops to work and there does not seem be any examples like this on the web, if someone can help me fix my code I would appreciate it.

Sean - thanks in advance


Msg 2370, Level 16, State 1, Line 24
XQuery [tblFeedStorage.XMLData.query()]: No more tokens expected at the end of the XQuery expression. Found 'for'.


declare @procount int
declare @counter int
declare @barcode varchar(200)
set @procount = (select count(*) from [tblProducts_temp])
set @counter =1
WHILE (@counter < @procount)
BEGIN
   IF (@counter > @procount)
      BREAK
   ELSE
      set       @barcode = (select barcode from [tblProducts_temp]
            where rowid=@counter)      
                  declare @sqlvarbcode nvarchar(50)
                  set @sqlvarbcode = @barcode
                  SELECT XMLData.query('
            
                  for $barcodeV in //product/barcode
                          where $barcodeV[.!=""]
                         and $barcodeV[.=sql:variable("@sqlvarbcode")]
                     return
                              $barcodeV
            
            
                              for $j in //product/actors      
                              where $j[.!=""]
                               and $barcodeV[.=sql:variable("@sqlvarbcode")]
                              return
                                    concat(" ",$j,",",$j)
                                    
                  
                        ')
                   as Result
                  FROM   tblFeedStorage            
            set @counter=@counter+1
      CONTINUE
END


Start Free Trial