Post reply

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

  • 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

  • November 9, 2010 at 2:55 pm

    #1248609

    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

  • November 9, 2010 at 3:04 pm

    #1248614

    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

  • November 9, 2010 at 8:14 pm

    #1248658

    is there any records in that dual table?

  • November 9, 2010 at 8:47 pm

    #1248660

    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

  • November 10, 2010 at 6:27 am

    #1248808

    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

  • November 10, 2010 at 6:48 am

    #1248825

    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.

  • November 10, 2010 at 9:58 am

    #1249021

    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

  • November 10, 2010 at 10:09 am

    #1249030

    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

  • November 10, 2010 at 10:47 am

    #1249052

    Perfect!

    Thanks Gus.

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

    Ken

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

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