The "T" in datetime value in XML

While working on an integration project, client’s developer had asked about the “T” separator between date and time value in the datetime data type fields and also about the default date of 1900-01-01 which we have covered in our blog posts before – here.  Also, note that an empty string is different than NULL value – the default value for an empty datetime string is converted to 1900-01-01 00:00:00.000 but for a NULL value, unless you are using XSINIL or coalescing that field to be an empty string, that element will not be present in the output. You can read more on the difference between an empty string and NULL over here.

Regarding the “T” separator, that is the ISO8601 format and is the default way of representing the datetime data in XML.   When you generate the data out of the database for a datetime data-type field, you will automatically get the “T” separator – here is an example:

declare @local_xml_tbl table (col1 int identity, colx datetime)
insert into @local_xml_tbl (colx) values (getdate())
select * from @local_xml_tbl
FOR XML PATH ('Demo'), ROOT ('XMLDEMO')

And the output will be:

<XMLDEMO>
<Demo>
<col1>1</col1>
<colx>2009-06-09T08:08:34.007</colx>
</Demo>
</XMLDEMO>

As BOL states, this is ISO 8601 standard for combined date and time in UTC.   Now, what happens when we consume the data and shred an XML document to consume it in the relational system and that XML contains datetime fields in this format?  Does it still display the “T” after the shreding or does it take care of removing after we have applied one of the shreding methods to it like the nodes() method?  Let’s take a look at that using an example:

DECLARE @x xml

SET @x =’<VoteAgent>
<InstructAck>
<MtgNtfcnId>444</MtgNtfcnId>
<Details>
<Key>0987654321</Key>
<Shares>100</Shares>
<MeetingDateTime>1900-01-01T00:00:00</MeetingDateTime>
<RecordDateTime>1900-01-01T00:00:00</RecordDateTime>
<VoteAction>FOR</VoteAction>
</Details>
</InstructAck>
<InstructAck>
<MtgNtfcnId>999</MtgNtfcnId>
<Details>
<Key>45454545454</Key>
<Shares>657</Shares>
<MeetingDateTime>2009-01-12T20:10:00</MeetingDateTime>
<RecordDateTime>2008-12-22T10:10:00</RecordDateTime>
<VoteAction>FOR</VoteAction>
</Details>
</InstructAck>
</VoteAgent>’

select
T.VA.value (‘../MtgNtfcnId[1]‘,’varchar(20)’ ) AS Mtng_Notification,
T.VA.value(‘Key[1]‘, ‘bigint’) as Share_Holder_ID,
T.VA.value(‘Shares[1]‘, ‘numeric(15,5)’) as Votable_Shares,
T.VA.value(‘MeetingDateTime[1]‘, ‘datetime’) as Meeting_Date_Time,
T.VA.value(‘RecordDateTime[1]‘, ‘datetime’) as Record_Date_Time,
T.VA.value(‘VoteAction[1]‘, ‘varchar(10)’) as Vote_Action
from @x.nodes(‘//Details’) as T(VA)

Mtng_Notification    Share_Holder_ID      Votable_Shares                          Meeting_Date_Time       Record_Date_Time        Vote_Action

——————– ——————– ————————————— ———————– ———————– ———–

444                  987654321            100.00000                               1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 FOR

999                  45454545454          657.00000                               2009-01-12 20:10:00.000 2008-12-22 10:10:00.000 FOR

So, as you can see, the “T” is no longer present after we consumed the XML.

If for some reason, there is a need to display the datetime data without the “T” in XML, you can always use the CONVERT() function to convert the datetime field to a string with a different format.

References:

• Wiki Post on ISO 8601 – here.
• BOL article on the CONVERT() function – here.  Take a look at the different formats.  You will see 126 as the one for ISO 8601.

About these ads