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

This is a discussion on xsd:dateTime and sqltypes:datetime validations differ. Any idea why? Please help. - XML SOAP ; 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 ...

+ Reply to Thread
Results 1 to 4 of 4

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

  1. Default xsd:dateTime and sqltypes:datetime validations differ. Any idea why? Please help.

    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>'






  2. Default Re: xsd:dateTime and sqltypes:datetime validations differ. Any idea why? Please help.

    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>'





  3. Default Re: xsd:dateTime and sqltypes:datetime validations differ. Any idea why? Please help.

    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>'
    >
    >
    >
    >



  4. Default Re: xsd:dateTime and sqltypes:datetime validations differ. Any idea why? Please help.

    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>'
    >>
    >>
    >>
    >>

    >




+ Reply to Thread

Similar Threads

  1. How to convert a xml datetime value into sql datetime?
    By Application Development in forum XML SOAP
    Replies: 1
    Last Post: 07-13-2007, 08:57 AM
  2. Replies: 1
    Last Post: 03-08-2007, 10:30 AM
  3. datetime dt1 (less than or equal to) datetime dt2...
    By Application Development in forum CSharp
    Replies: 1
    Last Post: 03-01-2007, 10:15 PM
  4. Replies: 1
    Last Post: 02-27-2007, 12:15 PM
  5. How to convert xs:datetime into sql datetime type?
    By Application Development in forum XML SOAP
    Replies: 5
    Last Post: 09-11-2006, 02:24 PM