Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 3 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>')
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.

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

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.

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

Your Answer

 
discard

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.