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:

  1. The value
  2. Null
  3. 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 <value /> 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:

        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
    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            "<CustomColumnsCollection>
                     <Value />
SO 2           "<CustomColumnsCollection>
SO 3           "<CustomColumnsCollection>

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

Thanks in advance.

  • Thanks Scott - as you can see it's my first question, is there a preferred format?
    – TimNewman
    Commented Nov 21, 2017 at 12:06
  • Just update your question with a code block (similar to the code you've already posted) that has the XML sample and include another code block of the expected results. See How to create a Minimal, Complete, and Verifiable example Commented Nov 21, 2017 at 12:09
  • Thanks - I have accepted an answer but I will still edit the question to make it useful to anyone else who finds this.
    – TimNewman
    Commented Nov 21, 2017 at 12:26

1 Answer 1


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

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


------------------- ---------- ----------- ----------------------- ---------- -----------
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.

enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.