Using XmlSerializer to create an Xml document for SQL Server 2005 (having a date typed attribute)

[Henrik Dahl]

Hello!

I have an xml schema which has a date typed attribute. I have used xsd.exe
to create a class library for XmlSerializer. The result of
XmlSerializer.Serialize(...) should be passed as the value for the parameter
of an SqlCommand for inserting the xml document in a column of a table where
the column is typed to be of the same xml schema.

This all sounds simple, but SQL Server REQUIRES the timezone to be specified
for date values. How to force XmlSerializer.Serialize(...) to include the
timezone of the date typed attribute (XmlSerializer.Serialize(...) by
default does not include the timezone)?


Best regards,

Henrik Dahl

 

[Steven Cheng[MSFT]]

Hello Henrik,

From your description, you have a custom class(generate from a given XML
schema through xsd tool) that need to be xml serialized. The custom class
contains a DateTime member which will be serialized as an xml attribute.
However, you found the serialized date value is always lack of the timezone
offset, you're wondering how to force xmlserizer to output timezone offset,
correct?

Based on my understanding, in .net 2.0, the Datetime class has been
enhanced. first, it support two kinds of DateTime instance

** Local datetime object

**UTC datetime object

this can be specified at the creation time, e.g.

DateTime dt = new DateTime(DateTime.Now.Ticks, DateTimeKind.Local);

And for local DateTime instance, the xmlserlizer will serialize it with
timezone offset included. While for UTC datetime instance, it will not
contain the timezone offset. e.g

the following code snippet will given the output like;

=====code =====
StringWriter sw = new StringWriter();

DateTime date1 = DateTime.Now;
DateTime date2 = DateTime.UtcNow;

XmlSerializer xs = new XmlSerializer(typeof(DateTime));
xs.Serialize(sw, date1);

xs.Serialize(sw, date2);

textBox1.Text = sw.ToString();

sw.Close();
=======================

=========output========
<?xml version="1.0" encoding="utf-16"?>
<dateTime>2006-12-11T21:17:01.7868744+08:00</dateTime><?xml version="1.0"
encoding="utf-16"?>
<dateTime>2006-12-11T13:17:01.7868744Z</dateTime>

=====================

So you can find that if you create a local datetime object, you'll get the
xmlserlization output with the timezone offset. Do you think it is possible
that you force your class to hold a local datetime instance?(you can check
it through DateTime.Kind property)

If this is not doable in your scenario, you may consider create a custom
wrapper class to represent datetime and you can implement our own
XmlSerialization logic through IXmlSerizable interface.

Hope this helps some.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Henrik Dahl]

Mhr. Cheng,

Thank you very much for your response. Unfortunately I think there's an
error in the .NET framework. Your example works excellent, however there's
one drawback. The xsd type should be xs:date not xs:dateTime. Try to put
your two dates into a class, let's name it CDates. When you XmlSerialize an
instance of this class it still works excellently, i.e. the timezone is put.
Now add the attribute
"[System.Xml.Serialization.XmlAttributeAttribute(DataType="date")]" to each
of the two DateTime fields and try again. Now the timezone is NOT specified
in the xml document.

Doesn't that look as an error in the .NET framework, i.e. it does not put
the timezone when serializing DateTime values decorated with the attributed
mentioned above?


Best regards,

Henrik Dahl

 

[Ollie Riches]

It is not an error in the .Net framework, check out the definition of 'Date'
& 'dateTime' in the XSD specification:

http://www.w3schools.com/schema/schema_dtypes_date.asp

HTH

Ollie Riches

Mhr. Cheng,

Thank you very much for your response. Unfortunately I think there's an
error in the .NET framework. Your example works excellent, however there's
one drawback. The xsd type should be xs:date not xs:dateTime. Try to put
your two dates into a class, let's name it CDates. When you XmlSerialize
an instance of this class it still works excellently, i.e. the timezone is
put. Now add the attribute
"[System.Xml.Serialization.XmlAttributeAttribute(DataType="date")]" to
each of the two DateTime fields and try again. Now the timezone is NOT
specified in the xml document.

Doesn't that look as an error in the .NET framework, i.e. it does not put
the timezone when serializing DateTime values decorated with the
attributed mentioned above?


Best regards,

Henrik Dahl

Hello Henrik,

From your description, you have a custom class(generate from a given XML
schema through xsd tool) that need to be xml serialized. The custom class
contains a DateTime member which will be serialized as an xml attribute.
However, you found the serialized date value is always lack of the
timezone
offset, you're wondering how to force xmlserizer to output timezone
offset,
correct?

Based on my understanding, in .net 2.0, the Datetime class has been
enhanced. first, it support two kinds of DateTime instance

** Local datetime object

**UTC datetime object

this can be specified at the creation time, e.g.

DateTime dt = new DateTime(DateTime.Now.Ticks, DateTimeKind.Local);

And for local DateTime instance, the xmlserlizer will serialize it with
timezone offset included. While for UTC datetime instance, it will not
contain the timezone offset. e.g

the following code snippet will given the output like;

=====code =====
StringWriter sw = new StringWriter();

DateTime date1 = DateTime.Now;
DateTime date2 = DateTime.UtcNow;

XmlSerializer xs = new XmlSerializer(typeof(DateTime));
xs.Serialize(sw, date1);

xs.Serialize(sw, date2);

textBox1.Text = sw.ToString();

sw.Close();
=======================

=========output========
<?xml version="1.0" encoding="utf-16"?>
<dateTime>2006-12-11T21:17:01.7868744+08:00</dateTime><?xml version="1.0"
encoding="utf-16"?>
<dateTime>2006-12-11T13:17:01.7868744Z</dateTime>

=====================

So you can find that if you create a local datetime object, you'll get
the
xmlserlization output with the timezone offset. Do you think it is
possible
that you force your class to hold a local datetime instance?(you can
check
it through DateTime.Kind property)

If this is not doable in your scenario, you may consider create a custom
wrapper class to represent datetime and you can implement our own
XmlSerialization logic through IXmlSerizable interface.

Hope this helps some.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Henrik Dahl]

OK. So I'll be more precise. Isn't it an error that XmlSerializer does not
put a Z letter at the end of the date as a UTC DateTime is getting
serialized using xs:date in the same way as the XmlSerializer does when
serializing a DateTime as an xs:dateTime?

For Ollie, if you still do not think it's a mistake, can't you tell why it's
not a mistake, please?

My point is not that information about the timezone may simply be omitted,
which is valid according to the xsd definition of xs:date, as it was the
case for .NET 1.0 and .NET 1.1 as it's told that .NET 2.0 is explicit about
the timezone and SQL Server 2005 only accepts dates having an explicit
timezone.


Best regards,

Henrik Dahl

It is not an error in the .Net framework, check out the definition of
'Date' & 'dateTime' in the XSD specification:

http://www.w3schools.com/schema/schema_dtypes_date.asp

HTH

Ollie Riches

Mhr. Cheng,

Thank you very much for your response. Unfortunately I think there's an
error in the .NET framework. Your example works excellent, however
there's one drawback. The xsd type should be xs:date not xs:dateTime. Try
to put your two dates into a class, let's name it CDates. When you
XmlSerialize an instance of this class it still works excellently, i.e.
the timezone is put. Now add the attribute
"[System.Xml.Serialization.XmlAttributeAttribute(DataType="date")]" to
each of the two DateTime fields and try again. Now the timezone is NOT
specified in the xml document.

Doesn't that look as an error in the .NET framework, i.e. it does not put
the timezone when serializing DateTime values decorated with the
attributed mentioned above?


Best regards,

Henrik Dahl

Hello Henrik,

From your description, you have a custom class(generate from a given XML
schema through xsd tool) that need to be xml serialized. The custom
class
contains a DateTime member which will be serialized as an xml attribute.
However, you found the serialized date value is always lack of the
timezone
offset, you're wondering how to force xmlserizer to output timezone
offset,
correct?

Based on my understanding, in .net 2.0, the Datetime class has been
enhanced. first, it support two kinds of DateTime instance

** Local datetime object

**UTC datetime object

this can be specified at the creation time, e.g.

DateTime dt = new DateTime(DateTime.Now.Ticks, DateTimeKind.Local);

And for local DateTime instance, the xmlserlizer will serialize it with
timezone offset included. While for UTC datetime instance, it will not
contain the timezone offset. e.g

the following code snippet will given the output like;

=====code =====
StringWriter sw = new StringWriter();

DateTime date1 = DateTime.Now;
DateTime date2 = DateTime.UtcNow;

XmlSerializer xs = new XmlSerializer(typeof(DateTime));
xs.Serialize(sw, date1);

xs.Serialize(sw, date2);

textBox1.Text = sw.ToString();

sw.Close();
=======================

=========output========
<?xml version="1.0" encoding="utf-16"?>
<dateTime>2006-12-11T21:17:01.7868744+08:00</dateTime><?xml
version="1.0"
encoding="utf-16"?>
<dateTime>2006-12-11T13:17:01.7868744Z</dateTime>

=====================

So you can find that if you create a local datetime object, you'll get
the
xmlserlization output with the timezone offset. Do you think it is
possible
that you force your class to hold a local datetime instance?(you can
check
it through DateTime.Kind property)

If this is not doable in your scenario, you may consider create a custom
wrapper class to represent datetime and you can implement our own
XmlSerialization logic through IXmlSerizable interface.

Hope this helps some.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Steven Cheng[MSFT]]

Hello Henrik,

Thanks for your followup.

After some further research, I've found out what happened here. Actually,
both SQL Server 2005 and .NET framework(DateTime serialization) are right.
The issue here is that they're following different datetime standard.

** SQL Server 2005 adopt the ISO 8601 format and extend it to require that
Date value also include timezone info(actually this is optional for date
value, but required for time or datetime)

** .net framework will not persistent(since it is not necessary) for
(Datatype="date") DateTime instance when performing xml serialization. This
is correctly conform to the XML Datetype standard.

Here is a blog article on SQL Server 2005 team which also describe this:

#Using xs:datetime, xs:date and xs:time with Sql Server 2005 XML
http://weblogs.sqlteam.com/dmauri/archive/2005/09/15/7792.aspx

Therefore, for your scenario here, you may need to add some additional code
to customize the default DateTime serialization output. As the article
indicate, since SQL SERVER 2005 accept the following UTC date value:

2005-05-27Z

You can use a wrapper string property which always append an "Z" after the
output Date string. How do you think?



Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Henrik Dahl]

Mr. Cheng,

Don't we have these facts:
*) The XmlSerializer's Deserialize method should be the inverse of the
XmlSerializer's Serialize method, otherwise the value of XmlSerializer drops
significantly.
*) The xs:date type allows to specify the timezone.

Therefore the XmlSerializer's Serialize method should obviously specify the
timezone when the underlying DateTime has a timezone and omit timezone when
the underlying DateTime is specified without timezone.

My point is, that it's basically not a matter of low abstraction level
syntactical considerations at the XML abstraction level but more about
higher order business aspects as I've written about in the lines above.

The reason why the result of XmlSerializer's Serialize method cannot be
submitted to SQL Server 2005 is that XmlSerializer's Serialize method does
not adhere to the conditions I've written in the first lines of this
posting.

Mr. Cheng do you agree or, if not, how do you perceive it?

By the way, thank you for your e-mail.


Best regards,

Henrik Dahl

 

[Steven Cheng[MSFT]]

Hi Henrik,

Thanks for your followup.

yes, I agree that the root cause here is the XML Serializer's output XML
format for DateTime type does not match the format expected by SQL Server
2005, also since DateTime is a premitive type which can not be quite
customized, so far using the built-in XML seriailzation will not be quite
convenient for SQL 2005 XML query(contain DateTime value).

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Henrik Dahl]

Mr. Cheng,

Exactly, for xs:dateTime it works but for xs:date information is lost by
XmlSerializer's Serialize method. I suppose a change of XmlSerializer's
Serialize method to include the CLR's DateTime's timezone information should
be considered a correction. Don't you agree it should be considered to be a
correction to avoid loosing information? Is there a chance that such a
correction will make it into the next service pack of the .NET framework or
the like?


Best regards,

Henrik Dahl

 

[Steven Cheng[MSFT]]

Thanks for your followup Henrik,

For the timezone offset, you can see that it is an identification info for
time offset rather than Date, and according to the W3C xml date type, this
info does be optional. SQL Server 2005 XML's restriction on the date type
is not exactly targeting the W3C xml date type. That's the cause of the
mismatch between the SQL Server 2005 expectation and DOTNET XML
serialization. Anyway, I can help you forward this to some internal group
and get some further ideas from them. I'll update you if I get some
information. Also, you can submit your suggestion and request on our
product feedback site:

http://connect.microsoft.com/feedback/default.aspx?SiteID=210

any feedback or comments are appreciated.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steven Cheng[MSFT]]

Hello Henrik,

I've forwarded this comment to our internal techinical group, so far this
is a fixed behavior of .net framework's datetime xml serialization. Surely
you're welcome to submit the request to the feedback center(mentioned in my
last reply) to let the dev team be informed on this. Also, if you need any
further assistance or need more formal channel to contact on the dev team,
you can contact CSS directly.

http://msdn.microsoft.com/subscriptions/support/default.aspx.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.