Including NULL columns as empty elements in SELECT FOR XML

_{Friday, July 27, 2007 4:54 PM}

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.

_{Saturday, July 28, 2007 11:50 AM ✅Answered | 1 vote}

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

_{Sunday, July 29, 2007 1:16 AM}

Great! Thanks for your reply.

_{Tuesday, May 20, 2008 8:51 AM}

Hi guys,

What about going the other way? Taking your XML with an empty element and populating into a SQL table?

_{Thursday, September 30, 2010 8:12 AM}

You can retrieve the value from the xml into a table using this:

with xmlnamespaces( 'http://www.w3.org/2001/XMLSchema-instance' as xsi)
insert @tbl (name, value)
select
    fields.field.value('name[1][not(@xsi:nil = "true")]', 'sysname'),
    fields.field.value('value[1][not(@xsi:nil = "true")]', 'nvarchar(max)')
from @xml.nodes('//fields/field') fields(field);

This is to me by far the easiest way to retrieve the actual value from the xml, including the NULL values as they were put into the xml by the elements xsinil clause. All you need to do is add the xmlnamespace line in front of the statement and paste the [not(@xsi:nil = "true")] after the xpath that you would normally use to retrieve the value. This works for any data type the same way, as opposed to the nullif(value('xpathhere', 'typehere'), 'thedefaultforthistypehere') construct you see elsewhere posted to retrieve null values from xsi:nil-ed elements.