title: How to store ouput of FOR XML Path in a variable?
body: <p>I want to store the output of for XML Path in a variable. However this doesn't work:</p>
  
  <pre><code>Declare @Tab    dbo.SingleColumnTable
  
  Insert INTO @Tab
  
  Values(
      'Jack'
  )
  
  Insert INTO @Tab
  
  Values(
      'Armageddon'
  )
  
  Declare @RetVal varchar(8000) = ''
  
  Select Top 1 @RetVal = Data
  From
  (
      Select ';' + ' ' + Name as 'Data'
                 From @Tab t2
                 for xml path('')
  ) v  
  </code></pre>
  
  <p>As you can see I am trying to set the column name of returned output as Data but it doesn't work. I get error <code>Invalid column name 'Data'.</code></p>