NULL should mean the absence of a value. In a database table, you can define a table to contain a value or not by marking it as NULL.

In XML, you can achieve the same thing in one of two ways. First, you can leave the element out of your XML. Second is you can leave the element without a value. When SQL Server parses XML, however, it can cause some strange values. Take the following code snippet:

DECLARE  @test XML
SET @Test = '<root><ThisShouldBeNull /></root>'
SELECT @test.Value('data(/root/ThisShouldBeNull)[1]','int');
SELECT @test.Value('data(/root/ThisShouldBeNull)[1]','varchar(10)');
SET @Test = '<root></root>'
SELECT @test.Value('data(/root/ThisShouldBeNull)[1]','int');

I expected this to return three NULL statements before this morning. But I was surprised to find that it actually returned a 0 for the first SELECT, a space for the second select and a NULL only for the third.

As I was explaining this to Mike Amundsen he said that it does make a sort of sense, since the first two selects in XML should return that the element exists, even if it has no data. But casting it to have a default value doesn't make a lot of sense to either of us.

So, if you are using XML in SQL Server and need to evaluate whether a value is there or not, you need to make sure the element doesn't exist as an empty element to do it correctly.