Can the ROOT element in a select statement using FOR XML PATH be set using a variable?

Question

I have a query that creates an xml file. Currently, I have the ROOT element hard-coded. I would like to use a variable value to set this root element value but substituting the hard-coded string with the variable throws a syntax error of 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
    )

Can I use a variable in the function ROOT()?

Answer 1

You could do the replacement in a separate replace 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 cheat using concat()

Create some sample data

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

---

The SQL

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

Returns

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