ansaurus

Question

How to get DataSet.ReadXml to parse a DateTime attribute as a typed DateTime

Answer 1

+1 A:

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();
        }
    }

nbushnell 2010-02-18 22:10:26

What do you mean that XML can't be strongly typed? The problem is that your XML data does not contain a valid date. There is precisely one representation of dates in XML, and that isn't it. Look up the `xs:Date` format in XML Schema if you're interested in having your strings be considered dates by tools that expect XML to follow XML standards.

John Saunders 2010-02-23 23:27:42

Ok right, I will update my answer, you can type xml dates but not in the example because the date is in an odd format. It is a valid date (this is standard in the UK) just not a valid date per XML schema.

nbushnell 2010-02-24 15:19:03

Thanks for your answer, it helped. In the end I actually converted the date into the correct format using string manipulation in an xslt and then loaded an xsd in order to tell the dataset that it was a date.

Robin Day 2010-02-25 07:45:58

ansaurus

tags:

views:

answers:

How to get DataSet.ReadXml to parse a DateTime attribute as a typed DateTime

related questions