Including NULL columns as empty elements in SELECT FOR XML

Hi everyone,

I was wondering if it is possible for a SELECT FOR XML statement to map a row with a NULL value in a column to an empty element in XML?

For example, let's say I have the following table:

CREATE TABLE NetworkAdapter

(

ID int PRIMARY KEY

MacAddress char(17)

)

The table has one row with the values (10, NULL). Can I use the SELECT FOR XML statement to return the following XML:

<NetworkAdapter>

<ID>10</ID>

<MacAddress /> -- Or <MacAddress></MacAddress>, doesn't matter

</NetworkAdapter>

Is it possible to do this without using ISNULL on the MacAddress column? Or if not, how would you do it using ISNULL?

Another somewhat related question ... Is it possible to use the SELECT FOR XML statement to return a set of empty elements for a SELECT statement that has no results? Using the NetworkAdapter table with just that one row listed above, let's say I have the following query:

SELECT *

FROM NetworkAdapter

WHERE ID = '5'

This query returns no results, but I would like to use it in conjunction with FOR XML to return this:

<NetworkAdapter>

<ID />

<MacAddress />

</NetworkAdapter>

Thanks.

There is a directive ELEMENTS XSINIL that causes NULL database values to be returned as an empty element with the attribute xsi:nil="true" e.g.

Code Snippet

SELECT ID, MacAddress

FROM NetworkAdapter

FOR XML AUTO, ELEMENTS XSINIL;

will then return

Code Snippet

<NetworkAdapter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ID>1</ID>

<MacAddress xsi:nil="true" />

</NetworkAdapter>

for rows where MacAdress is NULL. See http://msdn2.microsoft.com/en-us/library/ms178079.aspx

|||

Great! Thanks for your reply.