CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jack
Jack

Reputation: 7557

How to store ouput of FOR XML Path in a variable?

I want to store the output of for XML Path in a variable. However this doesn't work:

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

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 Invalid column name 'Data'.

Upvotes: 9

Views: 14327

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166546

Try changing 

Select Top 1 @RetVal = Data 
From 
( 
    Select ';' + ' ' + Name as 'Data' 
               From @Tab t2 
               for xml path('') 
) v

to

Select @RetVal = 
( 
    Select ';' + ' ' + Name as Data
   From @Tab t2 
   for xml path('') 
)

SQL Fiddle DEMO

Upvotes: 16

Related Questions