SELECT ... FOR XML returns NULL in 2008 but works OK in 2005

Question

SELECT ... FOR XML returns NULL in 2008 but works OK in 2005

kl4576

Ten Centuries

Points: 1270
More actions
November 9, 2010 at 2:38 pm

#231323

The following code example returns NULL on SQL Server 2008 (in the context of msdb) but works fine in the context of a user database. On SQL Server 2005 it works in all contexts. I want this to work in the context of msdb, since it is a part of a stored procedure in msdb (so it's available to all user databases).
[font="System"]
DECLARE @XML XML
DECLARE @to VARCHAR(MAX)
DECLARE @copy VARCHAR(MAX)
DECLARE @blind VARCHAR(MAX)
DECLARE @subj NVARCHAR(255)
DECLARE @message NVARCHAR(MAX)
SET @to = 'name@domain.ca'
SET @copy = NULL
SET @blind = NULL
SET @subj = N'Subject'
SET @message = N'message body'
SELECT @XML = (
SELECT @to AS Recipient, @copy AS Copyto, @subj AS Subject, @message AS Body
FROM dual /* this is a dummy table (like in Oracle :-P) */
AS Email
FOR XML AUTO, TYPE, ELEMENTS
)
SELECT @xml
[/font]
The above code should return:
<Email><Recipient>name@domain.ca</Recipient><Subject>Subject</Subject><Body>message body</Body></Email
Ken

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

For XML in T-SQL drops null columns by default. You have to use the XSINIL option to get them. That's probably the problem.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kl4576 Ten Centuries Points: 1270 More actions · Answer 2

I actually expect to have NULL columns dropped, and not appear in the resulting XML.

The problem is that the whole variable @XML is set to NULL when [msdb] is the current database, bit not when a user database is current.

Ken

yewang80 SSC Rookie Points: 43 More actions · Answer 3

yewang80

SSC Rookie

Points: 43

November 9, 2010 at 8:14 pm

#1248658

is there any records in that dual table?

WayneS SSC Guru Points: 95460 More actions · Answer 4

This works for me in all databases on my SQL 2008 server:

DECLARE @XML XML,

@to VARCHAR(MAX),

@copy VARCHAR(MAX),

@blind VARCHAR(MAX),

@subj NVARCHAR(255),

@message NVARCHAR(MAX);

SET @to = 'name@domain.ca';

SET @copy = NULL;

SET @blind = NULL;

SET @subj = N'Subject';

SET @message = N'message body';

SELECT @XML = (SELECT @to AS Recipient,

@copy AS Copyto,

@subj AS Subject,

@message AS Body

FOR XML RAW, TYPE, ELEMENTS);

SELECT @xml;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

GSquared SSC Guru Points: 260824 More actions · Answer 5

kl4576 (11/9/2010)
I actually expect to have NULL columns dropped, and not appear in the resulting XML.
The problem is that the whole variable @XML is set to NULL when [msdb] is the current database, bit not when a user database is current.

Is there a "dual" table in your copy of msdb, and does it have 0 rows in it? If you're going to use a dummy table (you don't need to in MS SQL), it needs to have a row in it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sturner SSC-Insane Points: 22411 More actions · Answer 6

what happens when you just execute this statement:

SELECT @to AS Recipient, @copy AS Copyto, @subj AS Subject, @message AS Body

FROM dual

Might be a permissions issue.

The probability of survival is inversely proportional to the angle of arrival.

kl4576 Ten Centuries Points: 1270 More actions · Answer 7

Thank you yewang80 and Gus - you nailed it. The problem was that dual was empty in [msdb] but not in the user databases. Doh! I should have compared more closely to my 2005 environment.

I tried your method, Wayne, but it gives

<row><Recipient>name@domain.ca</Recipient><Subject>Subject</Subject><Body>message body</Body></row>

instead of

<Email><Recipient>name@domain.ca</Recipient><Subject>Subject</Subject><Body>message body</Body></Email>

As far as I can tell, the only way I can get the <Email>...<\Email> tags is to use the "AS Email" clause, but for this I need a FROM clause. Is there another way to accomplish this?

Ken

GSquared SSC Guru Points: 260824 More actions · Answer 8

Add "('Email')" after "RAW" to Wayne's code and it'll put that tag in there where you want it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kl4576 Ten Centuries Points: 1270 More actions · Answer 9

Perfect!

Thanks Gus.

That's a lot cleaner and simpler than using the FROM dual clause.

Ken