xml validation date type error
This is my first attempt to load some XML validated against a schema.
The schema is more complicated than this but I've been able to narrow it down to show the problem.
After I load the schema and create the table which are successful, I try to perform an insert. The insert fails with the following error. Any help would be appreciated.
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: '2006-08-08'. Location: /*:myReport[1]/*:Criteria[1]/@*:myDate
CREATE XML SCHEMA COLLECTION myrpt_xsd
AS
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="myReport">
<xs:complexType>
<xs:sequence>
<xs:element name="Criteria">
<xs:complexType>
<xs:attribute name="myNumber" type="xs:integer" use="required" />
<xs:attribute name="myDate" type="xs:date" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
GO
create table xml_rpt(
id integer identity primary key,
my_rpt xml(myrpt_xsd)
)
insert into xml_rpt
values('<?xml version="1.0" encoding="UTF-8" ?>
<myReport>
<Criteria myNumber="18718" myDate="2006-08-08" />
</myReport>')
The xsd:date type represents a 24 hour period so it also needs a time zone too. Things like
2006-08-08Z
or
2006-08-08+03:00
will work.
Dan
SQL Server 2005 requires xs:date, xs:time and xs:dateTime values to specify a timezone. As Dan has pointed out,
1) 2006-08-08Z is valid (Z = Zulu timezone)
2) 2006-08-08-08:00 is valid (Pacific Standard Time timezone)
3) 2006-08-08 is invalid since no timezone is specified
Thank you
Shankar
Program Manager, Microsoft SQL Server
Out of curiosity, does anyone know if there's an elegant way around .NET's default XML serialization for a date type?
Having a property like this:
DateTime _date = new DateTime( 2006, 10, 27 );
[XmlElement("date")]
public DateTime Date
{
get{ return _date; }
set{ _date = value; }
}
Results in an element value like:
<date>2006-10-27</date>
I haven't found a good way to work around the issue (I currently do some Regex trickery in the DAL).
actually , .NET xml schema Validation is support '2006-11-11'
but SQL2005 need timezone
since the deserialize process i can't control ,I change the datetype to xs:string
Hi zppro
Will be hard to check for valid dates then ...
I do not understand such a decision by MS.
Why do they not simply assume "2006-08-08+00:00" when they get "2006-08-08"
MS, there is real life data where timezone information is not needed at all.
And what about helpful error messages?
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: '2006-08-08'. Location: /*:myReport[1]/*:Criteria[1]/@*:myDate
woudn't this be much better:
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: '2006-08-08'. Location: /*:myReport[1]/*:Criteria[1]/@*:myDate
Microsoft has decided to insist on time zone information for Sql Server 2005 special flavour xs:date values.
So please add the magic '+00:00' to all your xs:date values, e.g. '2006-08-08+00:00'.
MS, what about a flag to suppress this behaviour?
orbit
Hi
Is there a SP or hotfix for this oddity?
orbit
Hi,
I have two comments on this post:
1.
http://www.w3.org/TR/2001/REC-xmlschema-2-20010502/#date
2.
If it is a company policy at Microsoft, it should be made clear in other product groups [e.g. infopath 2007, MOSS2007 form services]. This time its impossible to insert a form having a date field in its schema posted from Infopath directly into an xml field in SQL server. In addition infopath does not understand the Z as time zone ;)
Regards,
Zoltan
urz at 2007-8-30 >
SQL Server 2005's xs:date/xs:time and xs:dateTime types cannot support both timezone and non-timezone aware values for architectural reasons. I tried to get these architectural reasons removed but was unfortunately not able to. So we had to make a decision to either only support values without timezones or values with timezones (that we would normalize to Z). We actually did a survey in 2004 with about 600 early users of SQL Server 2005 and received a majority wanting us to require Z for all data types, given the constraints (I was arguing to require no timezones for xs:date and xs:time which I think is the more common scenario, but the majority of customers thought keeping it consistent is better).
I strongly urge you to file a request under http://connect.microsoft.com/sqlserver. While we are working on fixing this issue, having active user bugs helps us making the case that the fix should receive the proper prioritization (and allows me to tell people "I told you so" ;-)).
However, fixing this will not be a service pack or hotfix level item. The architecture changes needed are large enough that we have to do it at the feature level.
Best regards
Michael
I actually hold the opinion that there is sometimes a difference between what people ask for and what they really want. If you just go up and ask someone "do you want time zones" I could see the answer going either way but if you append the condition "even if it means having creating an incompatibility with .NET's XML serialization" then I suspect the answers would be a lot different.
Really this wouldn't be so bad if you simply added formatting capabilities to the .NET XML serialization attributes or, say, had an option in SQL Server/T-SQL to automatically convert DateTimes without timezones into a DateTime with the database server's current timezone, but I submitted my bug request nonetheless!
Thanks Michael.
PS - I came across this post after Googling running into same issue I had last October when I posted. The SQL Server XML date continues to be a pain in the butt!
