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'
WHERE name = 'Cedric'
SELECT '' AS 'Name',
'' AS 'DBName' ) AS D
FOR XML PATH('User'), ROOT('Users'), TYPE )), '<User><Name/><DBName/></User>', '')
This example uses
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:
CREATE FUNCTION xmlQuery()
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
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.