Is there a way I can send a NULL value to a SQL stored proc through XML?

Question

I'm wondering if there is a way I can send a NULL or DBNull.Value from my C# data service to a stored proc through some configuration xml parameter.

In the proc, I want to pull some values out of the xml as a bit but because the UI allows for a third state, the bit value coming in from the xml can be NULL in which case I want to ignore any SQL updates for that field.

        XElement xml = new XElement("XML");
        xml.Add(new XElement("SomeConfigValue", NULL));

It seems when I throw a NULL into the xml from the C#, it defaults to a node with no value.

<SomeConfigValue />

When I try to grab the value from the xml as a bit in the SQL the value is interpreted as a 0.

select isnull(@Configuration.value('/Configuration[1]/SomeConfigValue [1]', 'bit'), NULL)

That's all fine and good. It makes sense. I'm just curious if anyone can suggest ideas from which I can get this to work the way I'd like it to. Perhaps I can't rely on the SQL bit type but I am currently re-factoring to be strongly typed.

I'd like to be able to grab a NULL if the value coming in isn't a 1 or 0.

set @SomeConfigValue = isnull(@Configuration.value('/Configuration[1]/SomeConfigValue [1]', 'bit'), NULL)

The goal is to ignore updating that value if it is NULL, or not a 1 or 0 in this specific SQL bit type case.

        update MyTable
 set ConfigValue = 
            case 
    when @SomeConfigValue IS NULL then T.SomeConfigValue
    else @SomeConfigValue 
 end from SomeTable T

Answer 1

Here the methodic used by standard xml serialization - IsNullable. XElement does not have similar property, but you can add attribute "xsi:nil" by yourself, and test against it in the stored proc.

Answer 2

You might want to check out

http://blogs.msdn.com/denisruc/archive/2006/11/30/xsi-nil-magic-part-1-2.aspx

and

http://blogs.msdn.com/denisruc/archive/2006/12/04/xsi-nil-magic-part-2-2.aspx

which talk about xsi:nil magic in SQL Server 2005