How to return a null from an empty XML tag in SQL server using Xpath only?

I have an application that stores various user defined data points about a record in an XML column. I have no control over how these are stored or updated.

When I query a column it can return 1 of 3 values:

• The value

• Null

• An empty string

$64 question I would like to return those empty strings as nulls using xpath.

I've found a lot of answers about returning the null as an empty sting but nothing this way round.

They end up that way when someone has delete the value, and instead of deleting the tag it blanks it to a `` tag. The nulls occur when the value has never been set. They have not included xsi:nil.

I've done it with a case statement:

select
    so.SalesOrderId,
    Case
        when sopc.value('(Value)[1]','smalldatetime') IS null then Null
        when sopc.value('(Value)[1]','smalldatetime') ='' then Null
        Else sopc.value('(Value)[1]','smalldatetime')
    End as sopc
    From
    SalesOrders so
    Outer apply so.CustomColumns.nodes('/CustomColumnsCollection/CustomColumn[Name="ProjCompleted"]') sopc(sopc)

but this feels inefficient and this is not the only column so it makes the code long and harder to maintain.

Edit to include sample data

SalesOrderId    CustomColumns
SO 1            "
                   
                     ProjCompleted
                     1
                     
                   
                 "
SO 2           "
                  
                     ProjCompleted
                     1
                     '2017-11-21'
                  
                "
SO 3           "
                "

**Output**
Current      Desired
''           null
2017-11-21   2017-11-21
null         null

Thanks in advance.

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 = '';

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.

StackExchange Database Administrators Q#191392, answer score: 14