XML Schema Collection and Dates

Question

Post reply

XML Schema Collection and Dates

Khades

SSCrazy

Points: 2067
More actions
January 26, 2012 at 10:15 pm

#391417

Not sure if this question belongs here or in the XML forums.
I've been reading everywhere that SQL Server 2008 has made time zones optional on the dates in an XML.
I am working with SQL Server 2008 R2. When I enter a date (without the time, just date) in an xs:date field without the Z, it fails XML schema validation, it works fine if I add the Z or any other time zone "2005-08-31+08:00" works too.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Can you script a repro? This works fine for me:

DECLARE @schema xml =

N'<?xml version = "1.0"?>

<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema">

<xsd:element name="theDate">

<xsd:simpleType>

<xsd:restriction base="xsd:date">

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

</xsd:schema>

';

CREATE XML SCHEMA COLLECTION dbo.TestSchemaCollection AS @schema;

GO

DECLARE @x xml (DOCUMENT dbo.TestSchemaCollection);

SET @x = N'<?xml version = "1.0"?><theDate>2011-07-11</theDate>';

SELECT @x.value('theDate', 'date');

GO

DROP XML SCHEMA COLLECTION dbo.TestSchemaCollection;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Khades SSCrazy Points: 2067 More actions · Answer 2

I ran your script in 4 environments and it only passes in 1 without the Z, it passes all with the Z.

Can this be server version issue?

10.0.2734 Fails

10.0.2757 Fails

10.0.4000 Passes

10.0.5500 Fails

Khades SSCrazy Points: 2067 More actions · Answer 3

Ok, looks like the compatibility level of some databases are set to 2005, found the issue, thanks!

Paul White SSC Guru Points: 150467 More actions · Answer 4

Khades (1/27/2012)
I ran your script in 4 environments and it only passes in 1 without the Z, it passes all with the Z.
Can this be server version issue?

Could be, I tested on 10.50.2789 and a 2012 build. Odd that 2008 SP3 has moved backward w.r.t SP2. I've read various things about xml dates/times over the years, and certainly the ISO format is a good practice (CONVERT style 127) though the Z shouldn't really be required, in my opinion anyway.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 5

Khades (1/27/2012)
Ok, looks like the compatibility level of some databases are set to 2005, found the issue, thanks!

Aha! Thanks for letting me know, I'll remember that.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

BOR15K SSCertifiable Points: 5894 More actions · Answer 6

Khades (1/27/2012)
Ok, looks like the compatibility level of some databases are set to 2005, found the issue, thanks!

Thanks, Khades!

You have saved my day with the compatibility level issue!