SQL Server 2008 - Shredding XML to tables need to keep UNKNOWN DATE value as NULL

Question

Hello.

I was wondering if someone could help

I've successfully been able to import an XML document to a table with an XML data type in SQL Server2008, how ever when I try and shred from that table to a staging table any DATE values without an entered date are inserted to the staging table as 1900-01-01.

Is there a cleaver way i'm missing to stop it doing this and simply insert NULL instead. I could use NULLIF on the staging table and replace 1900-01-01 with null but I’m reluctant to do this in case there are genuine 1900-01-01 values.

My code looks something like this

SELECT tab.col.value('LastDate[1]','DATE') LastARD'

FROM   import.XMLCompanyDetail

 CROSS APPLY
          xmldata.nodes('//VN/CompanyList/Row') AS tab(col)

Many thanks

Please see below the example XML i'm importing

<?xml version="1.0" encoding="ISO-8859-1" ?>
<VN>
<CompanyList>
<Row num="1"><CoNum>7878</CoNum><CoName>ExampleName</CoName><DInc>1978-12-30</DInc><DDis></DDis></Row>
</CompanyList>
</VN>

The date DDIS should be NULL but rather when it imports to my staging table it inserts 1901-01-01 instead.

updated code to show what I mean

create table staging_table
(DInc DATE NULL, LastARD DATE NULL);

with XMLCompanyDetail as 
(
SELECT CAST('<?xml version="1.0" encoding="ISO-8859-1" ?> 
<VN>
<CompanyList>
<Row num="1"><CoNum>7878</CoNum><CoName>ExampleName</CoName><DInc>1978-12-30</DInc><DDis></DDis></Row> 
</CompanyList>
</VN>
' AS XML) AS xmldata
)

INSERT INTO Staging_Table
SELECT tab.col.value('DInc[1]','DATE') DInc,
tab.col.value('DDis[1]','DATE') LastARD
FROM XMLCompanyDetail
CROSS APPLY
xmldata.nodes('//VN/CompanyList/Row') AS tab(col)

SELECT * FROM Staging_table
drop table staging_table

Answer 1

Try to use datetime instead:

declare @xml xml
set @xml = ''

select @xml.value('LastDate[1]','datetime') 

Wait, this returns null. That means that upon insert to your staging table this null value gets overridden by the default setting in that staging table. So remove that if you don't want this behavior to occur.

Answer 2

Edit

Following Update to the question the following works but I'm not sure if there is a better way.

SELECT tab.col.value('DInc[1][. != '''']','DATE') DInc,
tab.col.value('DDis[1][. != '''']','DATE') LastARD
FROM XMLCompanyDetail
CROSS APPLY
xmldata.nodes('//VN/CompanyList/Row') AS tab(col)