MS SQL XML - Time Zone in xsd:date

Author

Time Zone in xsd:date

Chris

2006-11-20, 12:14 am

I'm trying to validate a date element against a schema collection where the
element is of the type xsd:date. I get an error of "XML Validation: Invalid
simple type value: '2006-05-14'". If I add a time zone like "-07:00" it (the
element) validates. According to W3C the time zone should be optional. What
am I doing wrong?

Thanks,
Chris Macleod

Kent Tegels

2006-11-20, 7:21 pm

Hello Chris,

> According to W3C the time zone should be optional.

XML uses ISO-8601 datetime format (see http://www.w3.org/tr/NOTE-datetime)
specifies that if you indicate a time, you must have a TZD. Since SQL Server
doesn't have independent date and time data types, the semantically correct
way to indicate a date is to include hours, minutes and seconds with a TZD.
It can be as of zero hours, but it should always have a TZD.

Just one of the many oddities in SQL Server 2005's XSD engine. :)

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Chris

2006-11-20, 7:21 pm

Hi Kent,

"> Just one of the many oddities in SQL Server 2005's XSD engine. :)"

Thanks for the response. I can understand SQL Server’s datetime data type
issue but my hang-up is receiving an XML date data type from non-Microsoft
sites using a nationally defined XSD. I was hoping to be able to use the
schema collection feature to validate incoming data. If that’s not possible
then it’s back to the drawing board for me.

Chris Macleod

Denis Ruckebusch [MSFT]

2006-11-20, 7:21 pm

As a workaround you can always store your XML documents as untyped XML or modify
your schema to use a string type for those elements and/or attributes where
you're expecting a date value.

What solution to use depends on the type of queries you plan on running against
your data.

Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:A68BF05D-FE37-4CD4-A3CC- 2BF72EE10273@microso
ft.com...
> Hi Kent,
>
> "> Just one of the many oddities in SQL Server 2005's XSD engine. :)"
>
> Thanks for the response. I can understand SQL Server's datetime data type
> issue but my hang-up is receiving an XML date data type from non-Microsoft
> sites using a nationally defined XSD. I was hoping to be able to use the
> schema collection feature to validate incoming data. If that's not possible
> then it's back to the drawing board for me.
>
> Chris Macleod
>

Kent Tegels

2006-11-21, 12:21 am

Hello Chris,

In addition to Robert's input, let's not forget that you can probably tweak
the schema to treat the value as a string, but bind it to a regular expression
that would force it to be at least patterned properly. Even something as
lame as this would probably be a good start:

\d{4}\-\d{2}\-\d{2}T\d{2}\:\d{2}\:\d{2}\.?\d*

I can only imagine that you'll find other things that SQL Server won't like
about a sophisticated schema. :)

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Chris

2006-11-21, 7:16 pm

Thanks to both of you, Kent and Denis, for your responses. If this is the
only issue I have with 2005's XML parsing, then I guess I'm doing OK.

Chris Macleod

Sponsored Links