Isabella Soprano En Cathouse

SQL Server 2005 standard stricter than the W3C XSD

Hello everyone!

Following a relevant question from a client, I tried to understand why an XSD schema seems perfectly valid to me generated an error inserting the data ...

Once isolated problem, I was able to perform the following test:

DROP TABLE TestXML

GO DROP XML SCHEMA COLLECTION TestDateRestriction

GO DROP XML SCHEMA COLLECTION TestDateRestrictionTimeZone

GO CREATE XML SCHEMA COLLECTION AS TestDateRestriction

'\u0026lt;? xml version = "1.0" encoding="utf-8"?>
<xsd:schema targetNamespace="http://tempuri.org/TestDateRestriction.xsd" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns="http://tempuri.org/TestDateRestriction.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="root">
<xsd:simpleType>
<xsd:restriction base="xsd:dateTime">
<xsd:pattern value=".+T[^Z+-\.]+" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:schema>'
GO
CREATE XML SCHEMA COLLECTION TestDateRestrictionTimeZone
AS
'<?xml version="1.0" encoding="utf-8"?>
<xsd:schema targetNamespace="http://tempuri.org/TestDateRestriction.xsd" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns="http://tempuri.org/TestDateRestriction.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="root">
<xsd:simpleType>
<xsd:restriction base="xsd:dateTime">
<xsd:pattern value=".+T[^+-\.]+Z" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element></xsd:schema>'
GO
CREATE TABLE TestXML
(
IdTB INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_TestXML PRIMARY KEY CLUSTERED,
XMLTB XML (DOCUMENT TestDateRestriction) NULL,
XMLTBTimeZone XML (DOCUMENT TestDateRestrictionTimeZone) NULL
)
GO
INSERT INTO TestXML (XMLTB) VALUES (N'<r:root xmlns:r="http://tempuri.org/TestDateRestriction.xsd">2005-03-22T08:35:00</r:root>') GO INSERT INTO

TestXML (XMLTBTimeZone) VALUES (N '\u0026lt;r:root xmlns:r="http://tempuri.org/TestDateRestriction.xsd"> 2005-03-22T08: 35:00 Z \u0026lt;/ r: root> ') GO


The result is the following: SQL Server 2005 does not allow dates without specifying the flag Time Zone.

Now, as part of DateTime types, the XSD standard does not indicate the presence of TimeZone specified in ISO 8601 as specified in the W3C specifications: http://www.w3. org/TR/2005/WD-xpath-datamodel-20050211 / # Storing -timezones ...
However, the above example proves definitely the choice of SQL Server to comply with ISO. This information is confirmed by this article from the MSDN: http://msdn2.microsoft.com/en-us/library/ms345115.aspx # sql25xmlbp_topic3 (see "Using xs: datetime, xs: date and xs : time ").

The only question that remains is the reason for this choice requires that poses a compatibility problem: I have the XSD is taxed and shared by many users and therefore can not be changed ...

Happy reading!