How to store ouput of FOR XML Path in a variable?
12,392
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
Related videos on Youtube
Author by
Jack
Updated on September 15, 2022Comments
-
Jack over 1 year
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'.