How to get DataType specific DataTable from XML

Question

How to get DataType specific DataTable from XML

This is my code

string XMLReportFormat =@"<?xml version="1.0" encoding="utf-8" ?>
<Table>
    <ReportBody>
      <TableRow>
        <ID>1</ID>
        <ParentID>1</ParentID>
        <Key>First1</Key>
      </TableRow>
      <TableRow>
        <ID>4</ID>
        <ParentID>1</ParentID>
        <Key>FirstChild4</Key>
      </TableRow>
    </ReportBody>
</Table>";

StringReader sReader = new StringReader(XMLReportFormat);
DataSet ds = new DataSet();

if (sReader == null)
    return null;
ds.ReadXml(sReader);

Here I am getting all DataType of columns as String. I want some columns like ID and ParentID as Integer

Answer 1

sorry try this TryParse

http://msdn.microsoft.com/en-us/library/f02979c7.aspx

  DataRow dr = ...;
  int number;
  bool result = Int32.TryParse(dr["column"], out number);
  if (result)
  {
     // is a number , you can use it        
  }
  else
  {
     // convert your data to String
  }

Answer 2

Yeah ! I found the answer.

private static DataTable CreateDataTable()
{
    DataTable dt = new DataTable("TableRow");
    dt.Columns.Add(new DataColumn("ID", typeof(int)));
    dt.Columns.Add(new DataColumn("ParentID", typeof(int)));
    return dt;
}
public static DataTable GetReportFormatFromXML(string XMLReportFormat)
{
    if (XMLReportFormat == string.Empty)
        return null;
    StringReader sReader = new StringReader(XMLReportFormat);
    DataSet ds = new DataSet();

    if (sReader == null)
        return null;
    ds.Tables.Add(CreateDataTable());
    return ds.Tables[0];
}

This line helps me :)

ds.Tables.Add(CreateDataTable());

Answer 3

The easiest way I have found is make sure your DataSet reads an xml schema. The schema will set the data types of the XML for your DataSet.

DataSet.ReadXmlSchema

i.e.:

DataSet ds = new DataSet();
ds.ReadXmlSchema(Server.MapPath("/YourSchema.xsd"));
ds.ReadXml(Server.MapPath("/YourXML.xml"));

I was having an issue using Dynamic data in Microsoft Reports because everything coming from the XML was a string. I tried copying items to a new DataSet with dataTypes defined but this was much easier. You can have c# write a default schema for any xml file that you can then edit as needed.

DataSet ds = new DataSet();
ds.ReadXml("Data.xml");
ds.WriteXmlSchema("Data.xsd");

I know this post is old but it helped me out so I thought I would share what I learned.