.value() will actually return a NULL value if you have Typed XML. If you create an XML schema with nillable=true then .value will actually handle all the hard work.

i.e)

<?xml version="1.0" encoding="utf-8"?>

<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">

<xs:element name="BusinessFunction">

<xs:complexType>

<xs:sequence>

<xs:element name="Business_Function_Id" type="xs:int" maxOccurs="1" minOccurs="1" />

<xs:element name="Parent_Business_Function_Id" type="xs:int" maxOccurs="1" minOccurs="0" nillable="true" />

<xs:element name="Name" type="xs:string" maxOccurs="1" minOccurs="0" />

<xs:element name="Description" type="xs:string" maxOccurs="1" minOccurs="0" nillable="true" />

<xs:element name="Begin_Dt" type="xs:dateTime" maxOccurs="1" minOccurs="0" />

<xs:element name="End_Dt" type="xs:dateTime" maxOccurs="1" minOccurs="0" nillable="true" />

<xs:element name="Update_Dt" type="xs:dateTime" maxOccurs="1" minOccurs="0" />

<xs:element name="Delete_Dt" type="xs:dateTime" maxOccurs="1" minOccurs="0" nillable="true" />

<xs:element name="TimeStamp" type="xs:base64Binary" maxOccurs="1" minOccurs="0" nillable="true" />

</xs:sequence>

</xs:complexType>

</xs:element>

As you can see my Delete_Dt and End_Dt columns have a nullable="true" attribute.

So when you actually call value('Delete_Dt', 'dateTime') it will return a NULL value instead of the default.

i.e)

declare @xml xml (TrackingXML)

SET @xml = '<BusinessFunction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Business_Function_Id>0</Business_Function_Id><Delete_Dt xsi:nil="true" /></BusinessFunction>'

SELECT x.[BusinessFunction].value('Delete_Dt', 'dateTime')

FROM @xml.nodes('/BusinessFunction') x([BusinessFunction])

Give it a shot,

Raul