How to return a null from an empty XML tag in SQL server using Xpath only?
Specify the text() node within the Value element. That node is missing when you have an empty tag.

Try this:

declare @X xml;
set @X = '&lt;Value/&gt;';

select @X.value('(Value/text())[1]', 'smalldatetime'),
       @X.value('(Value/text())[1]', 'varchar(max)'),
       @X.value('(Value/text())[1]', 'int'),
       @X.value('(Value)[1]', 'smalldatetime'),
       @X.value('(Value)[1]', 'varchar(max)'),
       @X.value('(Value)[1]', 'int');


Result:

------------------- ---------- ----------- ----------------------- ---------- -----------
NULL                NULL       NULL        1900-01-01 00:00:00                0


It is a good thing in general to always specify the text() node. The query plan is simpler and more efficient.