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:

&lt;Users&gt;
&lt;FirstName&gt;Ammy&lt;/FirstName&gt;
&lt;LastName&gt;Dammy&lt;/LastName&gt;
&lt;/Users&gt;
&lt;Users&gt;
&lt;FirstName&gt;Lammy&lt;/FirstName&gt;
&lt;LastName&gt;Lolly&lt;/LastName&gt;
&lt;/Users&gt;


Expected Output on Empty table:

&lt;Users&gt;&lt;/Users&gt;


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, '&lt;Users&gt;&lt;/Users&gt;')
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:

&lt;Users&gt;&lt;/Users&gt;


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 (&lt;Users&gt;&lt;/Users&gt;) else the generated XML string is the result.