Dating the XML Serializer

Something came up at work yesterday that proved to be a tough nut to crack: when you use the .NET XMLSerializer to serialize an object that contains a DateTime property, the serializer will convert it to an offset based on your time zone.  That means that DateTime.Now will end up getting serialized into something like 2015-02-05T08:23:31.0858835-07:00.  While that's not necessarily bad by itself, it may cause problems depending on why you're serializing the object in the first place.  We serialize our objects and pass the resulting XML as a parameter to a stored procedure in SQL Server.  Then we use SQL Server's built-in XQuery support to get the values out that we need.

The problem we ran into was that when SQL Server retrieves that DateTime value (which, remember, is now an offset), and you convert it to DATETIME in SQL it converts the value to UTC time, which probably isn't what you intended.  So the sample I used above would get converted to '2015-02-05 15:28:59.903'.  If you didn't intend to store a UTC date (which, if you did, you probably should have just set the UTC date in .NET) that value is going to be wrong.  There's a simple solution for this that took me a while to figure out.  Retrieve the value from the XML as a DATETIMEOFFSET, then convert the resulting value to a DATETIME.  Check out the sample code below.

   1:  public class SomeDateTimeContainingObject

   2:  {

   3:      public DateTime CreationDate { get; set; }

   4:   

   5:      public int Id { get; set; }

   6:   

   7:      public string AsXml(bool shouldRemoveNull = true)

   8:      {

   9:          var xmlResult = "";

  10:   

  11:          var settings = new XmlWriterSettings();

  12:          settings.Encoding = new UnicodeEncoding(false, false);

  13:          settings.Indent = true;

  14:          settings.OmitXmlDeclaration = true;

  15:          var xmlSerializer = new System.Xml.Serialization.XmlSerializer(this.GetType());

  16:          using (var stringWriter = new StringWriter())

  17:          {

  18:              using (var xmlWriter = XmlWriter.Create(stringWriter, settings))

  19:              {

  20:                  xmlSerializer.Serialize(xmlWriter, this);

  21:              }

  22:   

  23:              //Strip out namespace info

  24:              xmlResult = stringWriter.ToString().Replace("'", "''").Replace("xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"", "").Replace("xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"", ""); //This is the output as a string

  25:          }

  26:   

  27:          //Load the XML doc

  28:          var xdoc = new XmlDocument();

  29:          xdoc.LoadXml(xmlResult.Replace("xsi:nil", "nullable"));

  30:   

  31:          if (shouldRemoveNull)

  32:          {

  33:              //Remove all NULL values 

  34:              foreach (XmlNode node in xdoc.SelectNodes("//*[@nullable]"))

  35:              {

  36:                  node.ParentNode.RemoveChild(node);

  37:              }

  38:          }

  39:   

  40:          return xdoc.OuterXml;

  41:      }

  42:  }

var someObject = new SomeDateTimeContainingObject

{

    CreationDate = DateTime.Now,

    Id = 123456

};

var xml = someObject.AsXml();

DECLARE @XML XML =
'<SomeDateTimeContainingObject><CreationDate>2015-02-05T08:23:31.0858835-07:00</CreationDate><Id>123456</Id></SomeDateTimeContainingObject>'

-- Wrong result
SELECT id.node.value('(CreationDate/text())[1]', 'DATETIME')
FROM   @XML.nodes('SomeDateTimeContainingObject') id(node)

-- Right result
SELECT CONVERT(DATETIME, id.node.value('(CreationDate/text())[1]',
                         'DATETIMEOFFSET'))
FROM   @XML.nodes('SomeDateTimeContainingObject') id(node)