Sql-server – SQL Xml returning empty root tag on empty record set

schemaselectsql-server-2005xml

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:

<Users>
<FirstName>Ammy</FirstName>
<LastName>Dammy</LastName>
</Users>
<Users>
<FirstName>Lammy</FirstName>
<LastName>Lolly</LastName>
</Users>

Expected Output on Empty table:

<Users></Users>

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

Best Answer

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, '<Users></Users>')
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:

<Users></Users>

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

Related Question