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 <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:
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 "<CustomColumnsCollection>
<CustomColumn>
<Name>ProjCompleted</Name>
<DataType>1</DataType>
<Value />
</CustomColumn>
</CustomColumnsCollection>"
SO 2 "<CustomColumnsCollection>
<CustomColumn>
<Name>ProjCompleted</Name>
<DataType>1</DataType>
<Value>'2017-11-21'</Value>
</CustomColumn>
</CustomColumnsCollection>"
SO 3 "<CustomColumnsCollection>
</CustomColumnsCollection>"
**Output**
Current Desired
'' null
2017-11-21 2017-11-21
null null
Thanks in advance.
Best Answer
Specify the
text()
node within theValue
element. That node is missing when you have an empty tag.Try this:
Result:
It is a good thing in general to always specify the
text()
node. The query plan is simpler and more efficient.