Geek asks and answers

How to store FOR XML Path output in a variable?

I want to save the output for the XML Path in a variable. However, this does not 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 the returned output as Data, but it does not work. I get an errorInvalid column name 'Data'.

+5
source share
1 answer

Try to change

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

+9
source

More articles:


All Articles