Using xs:datetime, xs:date and xs:time with Sql Server 2005 XML

While i'm was experimenting with Infopath and SQL Server 2005 ability to handle XML, i've found a not-expected behaviour (at least to me), that is better to explain a little bit so that no-one reading my blog will have the same problems.

The subject is datime and its XML representation. If you save a document with Infopath that contains date and time data you should have an output that is like the following:

<mtg:startDate xmlns:xsi="">2005-09-26</mtg:startDate>
<mtg:startTime xmlns:xsi="">10:00:00</mtg:startTime>
<mtg:endDate xmlns:xsi="">2005-09-26</mtg:endDate>
<mtg:endTime xmlns:xsi="">11:00:00</mtg:endTime>

Which is perfectly correct and aderent to the XML Standard, but SQL Server 2005 won't let you use that document, compelling about a wrong datetime format.

The answer can be found in this excellent whitepaper, where i've taken out the important piece:

Using xs:datetime, xs:date and xs:time

Values of type xs:datetime, xs:data and xs:time must be specified in ISO 8601 format and include a time zone. Otherwise the data validation for these values fails. Thus, 2005-05-27T14:11:00.943Z is valid as a value of type xs:datetime but the following are not: 2005-05-27 14:11:00.943Z (missing date and time separator "T"), 2005-05-27T14:11:00.943 (missing time zone) and 2005-05-27 14:11:00.943 (missing time separator and time zone). Similarly, 2005-05-27Z is a valid xs:date value but 2005-05-27 is not since no time zone is specified.

Untyped XML data may contain date, time and datetime values that an application may wish to convert to the SQL types dateTime or smallDateTime. These date, time and datetime values may not conform to ISO 8601 format or contain a time zone. Similarly, typed XML may contain such values as types other than xs:date, xs:time and xs:dateTime (e.g., xs:string). In both cases, the values should be converted first to [n]varchar and then to SQL datetime or smalldatetime, as the following example illustrates.


Keep this in  mind and you'll avoid some headaches!

posted on Thursday, September 15, 2005 9:43 AM Print