xsi:nil


It is not easy in XML to handle NULL values. You can of course add a custom attribute to every element to tell if the value should be NULL or not. Or, you can make proper use of the XSI:NIL attribute.
See this example

You expect think this should return two rows? One row with 0 (because empty space is converted to zero) and one row with NULL?
This is what you get.

What SQL Server need is an addition of a schema to handle NULL value.

See this example

And now you get two rows with zero. It is not really the result you expect either.

What you need is a mechanism to investigate the metadata attribute and somehow make a decision from that. This is probably the easiest way to accomplish that

And yes, we finally get the result we are looking for, one row with 0 and one row with NULL.

But think again, how often do we deal with single XML documents? Can we apply the same algorithm when the XML document is stored in a table?

This will work well. Now, what if we add an xsd-schema?

And then you get this error, even if the value element is nillable in the XSD schema.

How to get around that? By adding a xquery-function