SQL Xml returning empty root tag on empty record set

I am working on xml explicit to generate user defined nodes in xml output using Sql Server 2005 Express edition. The below code works fine, just that i couldn't manage to generate a empty node when no record set exists in the Users table.

CODE:

select 1 as tag, null as parent,

FirstName as [User!1!FirstName!Element],

LastName as [User!1!LastName!Element]

FROM Users

FOR XML EXPLICIT

OUTPUT:

Ammy
Dammy

Lammy
Lolly

Expected Output on Empty table:

Note: I don't like the xsnil approach, all other approaches to solving the problem is welcome

A possible solution to your question is neither elegant or clean (IMHO), but it does satisfy what you are looking for:

SELECT ISNULL(s.lmx, '')
FROM 
    (
        select 1 as tag, null as parent,
        FirstName as [User!1!FirstName!Element],
        LastName as [User!1!LastName!Element]
        FROM Users
        FOR XML EXPLICIT
    ) AS s(lmx)

The output for this on my system is:

Please let me know what you think.

Edit:

I took your select statement and made it a subquery. The subquery is run first and generates the output XML string or NULL. If the result is NULL, the outer SELECT replaces the NULL-value with an empty Users node (``) else the generated XML string is the result.

StackExchange Database Administrators Q#3848, answer score: 6