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
![](https://www.sqltopia.com/wp-content/uploads/2022/12/image.png)
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.
![](https://www.sqltopia.com/wp-content/uploads/2022/12/image-1.png)
What SQL Server need is an addition of a schema to handle NULL value.
See this example
![](https://www.sqltopia.com/wp-content/uploads/2022/12/image-2-1024x78.png)
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
![](https://www.sqltopia.com/wp-content/uploads/2022/12/image-3.png)
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?
![](https://www.sqltopia.com/wp-content/uploads/2022/12/image-5.png)
This will work well. Now, what if we add an xsd-schema?
![](https://www.sqltopia.com/wp-content/uploads/2022/12/image-9.png)
And then you get this error, even if the value element is nillable in the XSD schema.
![](https://www.sqltopia.com/wp-content/uploads/2022/12/image-8.png)
How to get around that? By adding a xquery-function
![](https://www.sqltopia.com/wp-content/uploads/2022/12/image-10.png)