Is it possible to specify a ROOT element in a select statement using FOR XML PATH using a variable?

Question

I have a request that creates an xml file. I currently have a hard-coded ROOT element. I would like to use the value of a variable to set this value of the root element, but substituting a gated string for the variable gives a syntax error expecting a string. This is the select statement:

DECLARE @SelectResults XML
DECLARE @DatabaseName varchar(100) 
SELECT @DatabaseName = DB_NAME();

    SET @SelectResults =
    ( 
    SELECT...query results here...
    FOR XML PATH(''),
    ROOT(@DatabaseName) --when this is set to 'DatabaseName' it works
    )

Is it possible to use a variable in a function ROOT()?

Answer 1

You can make a replacement in a separate replacement acting on the XML output:

DECLARE @SelectResults XML
DECLARE @DatabaseName varchar(100) 
SELECT @DatabaseName = DB_NAME();

    SET @SelectResults =
    replace( 
        SELECT...query results here...
        FOR XML PATH(''),
        ROOT('ROOT_ELEMENT') --when this is set to 'DatabaseName' it works
    ), 'ROOT_ELEMENT>',@DatabaseName+'>' )

Answer 2

Here is a little cheating using concat ()

Declare @SomeTable table (id int,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @SomeTable values
(1,'John','Smith','[e-mail]'),
(2,'Jane','Doe'  ,'[e-mail]')

---

SQL

Declare @SelectResults XML = concat('<',DB_NAME(),'>',(Select * from @SomeTable for XML Path),'</',DB_NAME(),'>') 
Select @SelectResults

<Chinrus-Dev>
  <row>
    <id>1</id>
    <First_Name>John</First_Name>
    <Last_Name>Smith</Last_Name>
    <EMail>[e-mail]</EMail>
  </row>
  <row>
    <id>2</id>
    <First_Name>Jane</First_Name>
    <Last_Name>Doe</Last_Name>
    <EMail>[e-mail]</EMail>
  </row>
</Chinrus-Dev>

Answer 3

, ad-hoc

SELECT 'test'
FOR XML PATH('x'),ROOT('y')

<y>
  <x>test</x>
</y>

DECLARE @root NVARCHAR(MAX)='z';
SELECT 'test'
FOR XML PATH('x'),ROOT(@root) <-- error!

SQL

DECLARE @root NVARCHAR(MAX)='z';

DECLARE @cmd NVARCHAR(MAX)=
(
'SELECT ''test''
FOR XML PATH(''x''),ROOT(''' + @root + ''')'
);
EXEC(@cmd);

<z>
  <x>test</x>
</z>

But - as always - dynamic SQL has some heavy back spins ...