Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


select 1 as tag, null as parent,

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

LastName as [User!1!LastName!Element]

FROM Users




Expected Output on Empty table:


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

share|improve this question
up vote 6 down vote accepted

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>')
        select 1 as tag, null as parent,
        FirstName as [User!1!FirstName!Element],
        LastName as [User!1!LastName!Element]
        FROM Users
    ) AS s(lmx)

The output for this on my system is:


Please let me know what you think.


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.

share|improve this answer
could you put in few sentences on how the code does it, i am unable to get the logic there. – Deeptechtons Jul 18 '11 at 5:30
Added a brief description under "Edit:". – Robert Miller Jul 18 '11 at 7:12
accepted the answer. Thanks for the info and it does work well. Now off to Code Lounge to get it more optimized – Deeptechtons Jul 18 '11 at 11:07
For my situation, I wanted the result to be an XML type, so changing the first line to this worked well: SELECT CAST(ISNULL(s.lmx, '<Users></Users>') AS XML) – TTT Mar 24 at 16:15

This is as ugly as sin, but might help:

                                      FROM   ( SELECT name   AS 'Name',
                                                      dbname AS 'DBName'
                                               FROM   syslogins
                                               WHERE  name = 'Cedric'
                                               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:


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:

RETURNS @xmlTable TABLE ( results XML NOT NULL )
    DECLARE @records BIGINT
    DECLARE @xmlData XML

    SELECT @records = COUNT(*) FROM syslogins

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

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


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.

share|improve this answer
God i ain't gonna do that ever – Deeptechtons Jul 14 '11 at 11:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.