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:startTime xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">10:00:00</mtg:startTime>
<mtg:endDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2005-09-26</mtg:endDate>
<mtg:endTime xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">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!
Legacy Comments
|
Michael Rys
2005-09-16 |
re: Using xs:datetime, xs:date and xs:time with Sql Server 2005 XML Your code sample is not showing. |
|
Davide Mauri
2005-09-17 |
re: Using xs:datetime, xs:date and xs:time with Sql Server 2005 XML Ehm....what do you mean? |
|
T
2005-09-21 |
re: Using xs:datetime, xs:date and xs:time with Sql Server 2005 XML He means what problem did it solve. Rather than regurgitate documentation, show us what problem you were encountering and why this documentation helped you solve the problem. |
|
Davide Mauri
2005-09-21 |
re: Using xs:datetime, xs:date and xs:time with Sql Server 2005 XML Thanks for the explanation. I'll do as you suggests as soon as possibile. |
|
Jânio
2005-10-18 |
re: Using xs:datetime, xs:date and xs:time with Sql Server 2005 XML Does this mean you can't use the method SaveToFile('name.xml',dfXMLUTF8) in Delphi beacuse it will not write the time zone. |