SQL Server pass variable instead of hard coding root element name

Question

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

Answer 1

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

Answer 2

It's worth learning to read the documentation for SQL Server. If you look at the FOR clause you'll see:

| PATH [ ( 'ElementName' ) ] 
...
[ , ROOT [ ( 'RootName' ) ] ]

If you compare this with other documentation, here I'll use as an example BACKUP:

 { 'physical_device_name' | @physical_device_name_var }
...
FILE = { logical_file_name | @logical_file_name_var } 
| FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

That is, when it's valid to pass only a string, they'll only show a '' delimited value in the syntax diagram. When it's possible to pass a variable, they'll explicitly show it as an option (with a @ name). And when you have to provide a name rather than a string, they'll show it without '' marks

So, as @Devart says, the only possibility is to use dynamic SQL.