xsd:dateTime and sqltypes:datetime validations differ. Any idea why? Please help. - XML SOAP

From MSDN:

"In SQL Server 2005, all types derived from xs:date, xs:time, and
xs:dateTime are required to have time zones. Sqltypes:datetime and
sqltypes:smalldatetime are two of these types. However, the SQL datetime and
smalldatetime types do not have time zones. This is because the pattern
facets for sqltypes:datetime and sqltypes:smalldatetime do not allow for
time zones. As a result, SQL Server does not accept sqltypes:datetime or
sql:smalldatetime values. Although you can reference sqltypes:datetime and
sqltypes:smalldatetime in user-defined schemas, you cannot validate XML
documents that contain values of these types. This makes them unusable."

http://msdn2.microsoft.com/en-us/library/ms190665.aspx



"Farmer" <someone@somewhere.com> wrote in message
news:0F154946-A80C-4990-9E4C-076A36F5F735@microsoft.com...
According to

http://connect.microsoft.com/SQLServ...dbackID=167649

dates(datetimes) need to have Z specified when schema validation is desired.
Using xsd:dateTime, it works.
Why no matter what I try, I cna't make SQL smalldatetime or datetime
derivaties to work?

Could someone have any idea?

thank you for your time.
farmer

IF EXISTS
(
SELECT *
FROM sys.xml_schema_collections c
JOIN sys.schemas s ON s.schema_id = c.schema_id
WHERE s.[name] = N'dbo'
AND c.[name] = N'TimeTest'
)
DROP XML SCHEMA COLLECTION TimeTest
GO

CREATE XML SCHEMA COLLECTION TimeTest
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
targetNamespace="TimeTest">
<xsd:import
namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"
/>
<xsd:element name="date" type="xsd:dateTime"/>
<xsd:element name="datesql" type="sqltypes:datetime"/>
</xsd:schema>'
go

-- works as expected
DECLARE @x xml(TimeTest)
SET @x='<date xmlns="TimeTest">2002-10-10T12:00:00Z</date>'

-- does not work

DECLARE @x2 xml(TimeTest)
--SET @x2='<datesql xmlns="TimeTest">9999-12-31T23:59:59Z</datesql>'
SET @x2='<datesql xmlns="TimeTest">9999-12-31T23:59:59.997Z</datesql>'

Thank you, Mike!

Very good explanation, even though the outcome sucks.

I am quite new to schemas but I have been mandated to make one, or more like
few;
I know it's bad combination but I am quite SQL guru with a lot of
experience, so it's good experience and I understand data well.
In your opinion, should I use MS derivative data type validation ?

<xsd:attribute name="PaymentCode">
<xsd:simpleType>
<xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033"
sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth"
sqltypes:sqlSortId="52">
<xsd:maxLength value="20" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>

versus (I hope I modified it right.)

<xsd:attribute name="PaymentCode">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>

thanks
farmer

"Mike C#" <xyz@xyz.com> wrote in message
news:eDXHDQSPIHA.5264@TK2MSFTNGP02.phx.gbl...
> From MSDN:
>
> "In SQL Server 2005, all types derived from xs:date, xs:time, and
> xs:dateTime are required to have time zones. Sqltypes:datetime and
> sqltypes:smalldatetime are two of these types. However, the SQL datetime
> and smalldatetime types do not have time zones. This is because the
> pattern facets for sqltypes:datetime and sqltypes:smalldatetime do not
> allow for time zones. As a result, SQL Server does not accept
> sqltypes:datetime or sql:smalldatetime values. Although you can reference
> sqltypes:datetime and sqltypes:smalldatetime in user-defined schemas, you
> cannot validate XML documents that contain values of these types. This
> makes them unusable."
>
> http://msdn2.microsoft.com/en-us/library/ms190665.aspx
>
>
>
> "Farmer" <someone@somewhere.com> wrote in message
> news:0F154946-A80C-4990-9E4C-076A36F5F735@microsoft.com...
> According to
>
> http://connect.microsoft.com/SQLServ...dbackID=167649
>
> dates(datetimes) need to have Z specified when schema validation is
> desired.
> Using xsd:dateTime, it works.
> Why no matter what I try, I cna't make SQL smalldatetime or datetime
> derivaties to work?
>
> Could someone have any idea?
>
> thank you for your time.
> farmer
>
> IF EXISTS
> (
> SELECT *
> FROM sys.xml_schema_collections c
> JOIN sys.schemas s ON s.schema_id = c.schema_id
> WHERE s.[name] = N'dbo'
> AND c.[name] = N'TimeTest'
> )
> DROP XML SCHEMA COLLECTION TimeTest
> GO
>
> CREATE XML SCHEMA COLLECTION TimeTest
> AS
> '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
> targetNamespace="TimeTest">
> <xsd:import
> namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
> schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"
> />
> <xsd:element name="date" type="xsd:dateTime"/>
> <xsd:element name="datesql" type="sqltypes:datetime"/>
> </xsd:schema>'
> go
>
> -- works as expected
> DECLARE @x xml(TimeTest)
> SET @x='<date xmlns="TimeTest">2002-10-10T12:00:00Z</date>'
>
> -- does not work
>
> DECLARE @x2 xml(TimeTest)
> --SET @x2='<datesql xmlns="TimeTest">9999-12-31T23:59:59Z</datesql>'
> SET @x2='<datesql xmlns="TimeTest">9999-12-31T23:59:59.997Z</datesql>'
>
>
>
>

I would stick with the XSD data types personally. Note also that you can
create your own types. If you wanted to store "date/time" type data without
the time zone you could use an xsd:string type with a pattern restriction
(regular expression). Note tht SQL 2008 offers more flexible date, time,
and date/time support.

Another interesting feature of SQL 2005 xs:dateTime support is that all
date/time values are converted and stored as UTC time (time zone Z). So
setting an xs:dateTime to 2002-10-10T12:00:00-05:00 (EST) actually stores
2002-10-10T17:00:00Z. I have no idea how, or if, they compensate for
Daylight Savings Time (not all governments recognize it) in this conversion.
This appears to have changed in SQL 2008, and the date/time is stored with
the time zone you specify originally.

"Farmer" <someone@somewhere.com> wrote in message
news:AAB13367-04EF-4095-92E1-E2D41660B626@microsoft.com...
> Thank you, Mike!
>
> Very good explanation, even though the outcome sucks.
>
> I am quite new to schemas but I have been mandated to make one, or more
> like few;
> I know it's bad combination but I am quite SQL guru with a lot of
> experience, so it's good experience and I understand data well.
> In your opinion, should I use MS derivative data type validation ?
>
> <xsd:attribute name="PaymentCode">
> <xsd:simpleType>
> <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033"
> sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth"
> sqltypes:sqlSortId="52">
> <xsd:maxLength value="20" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
>
> versus (I hope I modified it right.)
>
> <xsd:attribute name="PaymentCode">
> <xsd:simpleType>
> <xsd:restriction base="xsd:string">
> <xsd:maxLength value="20" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:attribute>
>
> thanks
> farmer
>
> "Mike C#" <xyz@xyz.com> wrote in message
> news:eDXHDQSPIHA.5264@TK2MSFTNGP02.phx.gbl...
>> From MSDN:
>>
>> "In SQL Server 2005, all types derived from xs:date, xs:time, and
>> xs:dateTime are required to have time zones. Sqltypes:datetime and
>> sqltypes:smalldatetime are two of these types. However, the SQL datetime
>> and smalldatetime types do not have time zones. This is because the
>> pattern facets for sqltypes:datetime and sqltypes:smalldatetime do not
>> allow for time zones. As a result, SQL Server does not accept
>> sqltypes:datetime or sql:smalldatetime values. Although you can reference
>> sqltypes:datetime and sqltypes:smalldatetime in user-defined schemas, you
>> cannot validate XML documents that contain values of these types. This
>> makes them unusable."
>>
>> http://msdn2.microsoft.com/en-us/library/ms190665.aspx
>>
>>
>>
>> "Farmer" <someone@somewhere.com> wrote in message
>> news:0F154946-A80C-4990-9E4C-076A36F5F735@microsoft.com...
>> According to
>>
>> http://connect.microsoft.com/SQLServ...dbackID=167649
>>
>> dates(datetimes) need to have Z specified when schema validation is
>> desired.
>> Using xsd:dateTime, it works.
>> Why no matter what I try, I cna't make SQL smalldatetime or datetime
>> derivaties to work?
>>
>> Could someone have any idea?
>>
>> thank you for your time.
>> farmer
>>
>> IF EXISTS
>> (
>> SELECT *
>> FROM sys.xml_schema_collections c
>> JOIN sys.schemas s ON s.schema_id = c.schema_id
>> WHERE s.[name] = N'dbo'
>> AND c.[name] = N'TimeTest'
>> )
>> DROP XML SCHEMA COLLECTION TimeTest
>> GO
>>
>> CREATE XML SCHEMA COLLECTION TimeTest
>> AS
>> '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
>> xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
>> targetNamespace="TimeTest">
>> <xsd:import
>> namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
>> schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"
>> />
>> <xsd:element name="date" type="xsd:dateTime"/>
>> <xsd:element name="datesql" type="sqltypes:datetime"/>
>> </xsd:schema>'
>> go
>>
>> -- works as expected
>> DECLARE @x xml(TimeTest)
>> SET @x='<date xmlns="TimeTest">2002-10-10T12:00:00Z</date>'
>>
>> -- does not work
>>
>> DECLARE @x2 xml(TimeTest)
>> --SET @x2='<datesql xmlns="TimeTest">9999-12-31T23:59:59Z</datesql>'
>> SET @x2='<datesql xmlns="TimeTest">9999-12-31T23:59:59.997Z</datesql>'
>>
>>
>>
>>
>