SQL Xml returning empty root tag on empty record set

Question

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

Answer 1

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.

Answer 2

This is as ugly as sin, but might help:

SELECT REPLACE(CONVERT(VARCHAR(MAX), (SELECT Name, DBName
                                      FROM   ( SELECT name   AS 'Name',
                                                      dbname AS 'DBName'
                                               FROM   syslogins
                                               WHERE  name = 'Cedric'
                                               UNION
                                               SELECT '' AS 'Name',
                                                      '' AS 'DBName' ) AS D
                                      FOR    XML PATH('User'), ROOT('Users'), TYPE )), '<User><Name/><DBName/></User>', '')

This example uses syslogins from master for source data, the WHERE name='Cedric' is used only to force an empty set on my server. Assuming you have no user called Cedric, the output will be:

<Users></Users>

Essentially, what this does is a UNION to a set containing only empty data values - NOT NULL values, converts the resulting XML to VARCHAR(MAX) and then removes the empty XML tags, resulting in an empty root element.

As I said, it's ugly as sin, but it does accomplish the end-goal you're looking for ...

An likely better alternative would be to place your query inside a UDF:

CREATE FUNCTION xmlQuery()
RETURNS @xmlTable TABLE ( results XML NOT NULL )
AS
BEGIN
    DECLARE @records BIGINT
    DECLARE @xmlData XML

    SELECT @records = COUNT(*) FROM syslogins

    IF @records > 0
    BEGIN
        SELECT @xmlData = ( SELECT name, dbname
                            FROM   syslogins
                            FOR    XML PATH('User'), ROOT('Users'), TYPE )
    END
    ELSE
    BEGIN
        SELECT @xmlData = CONVERT(XML, '<Users/>')
    END

    INSERT INTO @xmlTable ( results ) VALUES ( @xmlData )    

    RETURN
END

Then call the UDF via:

SELECT * FROM xmlQuery()

The problem with this approach is that you will need to define selection criteria for any WHERE clause you'll require as Parameters for the UDF. Place your own query in place of the stub I've used in the UDF for the case where there are records to return.