SQL Server pass variable instead of hard coding root element name

sql, sql-server

Solution

Try dynamic SQL -

DECLARE 
  @Root VARCHAR(20)
, @Element VARCHAR(20)
, @SQL NVARCHAR(500)

SELECT 
  @Root = 'Root'
, @Element = 'Element'
, @SQL = N'
 SELECT *
 FROM Labour.Absence a
 FOR XML PATH (''' + @Element + '''), ROOT(''' + @Root + ''')' 

PRINT @SQL

EXEC sp_executesql @SQL

Problem

In SQL Server 2008, I have is query ``` DECLARE @root AS varchar(20); SET @root = 'Root'; DECLARE @element AS varchar(20); SET @element = 'Element'; SELECT * FROM SomeTable FOR XML PATH (@element), ROOT(@root); ``` What I want is to pass a variables instead of hard coding the name of the repeating element and the root. When I execute the query, it throws this error: ``` Msg 102, Level 15, State 1, Line 11 Incorrect syntax near '@element'. ``` Is it possible? Thanks

Original source