How to get DataSet.ReadXml to parse a DateTime att

I have an XML string which contains Dates formatted "dd/MM/yyyy hh:mm:ss".

I'm loading this XML into a dataset using DataSet.ReadXml().

How can I ensure that this Date is stored in the DataSet as a typed DateTime so that I can Sort, Format and RowFilter on it accordingly.

My test harness is as below:

ASPX Page:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="XmlDateTypeList.aspx.cs" Inherits="Test.XmlDateTypeList" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Test</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server">
            <Columns>
                <asp:BoundField DataField="Name" HeaderText="Name" />
                <asp:BoundField DataField="Date" HeaderText="Date" DataFormatString="{0:dddd dd MMMM yyyy}" />
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Code Behind:

using System;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Linq;

namespace Test
{
    public partial class XmlDateTypeList : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            XDocument xDoc = new XDocument(
                new XElement("List",
                    new XElement("Item",
                        new XAttribute("Name", "A"),
                        new XAttribute("Date", "21/01/2010 00:00:00")
                    ),
                    new XElement("Item",
                        new XAttribute("Name", "B"),
                        new XAttribute("Date", "12/01/2010 00:00:00")
                    ),
                    new XElement("Item",
                        new XAttribute("Name", "C"),
                        new XAttribute("Date", "10/01/2010 00:00:00")
                    ),
                    new XElement("Item",
                        new XAttribute("Name", "D"),
                        new XAttribute("Date", "28/01/2010 00:00:00")
                    )
                )
            );

            DataSet dataSet = new DataSet();
            dataSet.ReadXml(new StringReader(xDoc.ToString()));

            GridView1.DataSource = dataSet.Tables[0];
            GridView1.DataBind();
        }
    }
}

This is painful but the root issue is that your xml data can't be strongly typed in this case because the date format is not in the xs:date format and you can't change the type once the data is in the data set. Further complicating things is that .NET wont auto format "28/01/2010 00:00:00" as a date. So, just a copy from one data table to another with the correct data type and reformatting the date string along the way. This code works, but is far from elegant. Good Luck.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Xml.Linq;
    using System.Data;
    using System.IO;

    public partial class xmltest : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            XDocument xDoc = new XDocument(
                new XElement("List",
                    new XElement("Item",
                        new XAttribute("Name", "A"),
                        new XAttribute("Date", "21/01/2010 00:00:00")
                    ),
                    new XElement("Item",
                        new XAttribute("Name", "B"),
                        new XAttribute("Date", "12/01/2010 00:00:00")
                    ),
                    new XElement("Item",
                        new XAttribute("Name", "C"),
                        new XAttribute("Date", "10/01/2010 00:00:00")
                    ),
                    new XElement("Item",
                        new XAttribute("Name", "D"),
                        new XAttribute("Date", "28/01/2010 12:33:22")
                    )
                )
            );

            DataSet dataSet = new DataSet();
            dataSet.ReadXml(new StringReader(xDoc.ToString()));

            DataSet dataSet2 = dataSet.Clone();

            dataSet2.Tables[0].Columns[1].DataType = typeof(DateTime);

            // painful, painful copy over code from dataset1 to dataset2 
            foreach (DataRow r in dataSet.Tables[0].Rows)
            {

                DataRow newRow = dataSet2.Tables[0].NewRow();
                newRow["name"] = r["name"];

                newRow["Date"] = DateTime.ParseExact(r["Date"].ToString(), "dd/MM/yyyy HH:mm:ss", CultureInfo.CurrentCulture);

                dataSet2.Tables[0].Rows.Add(newRow);

            }

            GridView1.DataSource = dataSet2.Tables[0];
            GridView1.DataBind();
        }
    }