Sql : Update a row with a concatened string obtained with xmlPath

Question

I have to update a table column with a result of a concatened string obtained with xmlpath.

For exemple, I have to do something like that

Update table c
set c.languages = 
(SELECT '\r\n'+rl.Name AS [text()] 
    from ResourceLanguage as rl 
        where rl.resource_Id=c.resource_Id
    FOR XML PATH (''))

I get an error 'Incorrect syntax near c'.

P.S : In my exemple, the field Languages is of type nvarchar(max)

Can somebody help me? Thanks

Answer 1

I found the solution. Here is it:

 DECLARE @tempTable TABLE
(
    Id int,
    Languages nvarchar(max)
)

INSERT @tempTable (Id, Languages )
(
    select r1.resource_Id, Stuff((select', '+ rl.Name as [text()]
      from ResourceLanguage as rl 
      where rl.resource_Id=c.resource_Id
      for xml path(''), type).value('.', 'nvarchar(250)'), 1, 2, '')
    from Resource r1
)

Update Resource 
Set Languages = temp.Languages 
    FROM @tempTable temp  
    where resource_Id=temp.resource_Id