none
Struggling with DataTable.ReadXML()

    Question

  • Hi guys,

    actually I've found a solution for my problem, I just wanted to share my experience and ask whether anybody knows why it wasn't implemented the way I would expect :).

    Example code:

    ...............

    System.Data.DataTable table1 = new System.Data.DataTable();

    table1.Columns.Add("Column1", typeof(int));

    for (int i = 0; i < 5000; i++)

    {

    table1.Rows.Add(i);

    }

    System.IO.StringWriter stringWriter = new System.IO.StringWriter();

    table1.WriteXml(stringWriter, System.Data.XmlWriteMode.WriteSchema, false);

    stringWriter.Flush(); //Just for a case

    DataTable anotherTable = new DataTable();

    anotherTable.ReadXml(stringWriter.ToString());

    ...............

     

    Looks pretty sensible, doesn't it? No at all! ;)

    1. First exception is System.InvalidOperationException on "table1.WriteXml()". We must explicitly set the TableName. FYI, when serializing a dataset, its DataSetName may be empty (will be written as NewDataSet in XML). But that would be too consistent! Of course, developers must keep in mind where they need to set some meaningless names, and where not. And of course there is nothing about it in documentation! :)

    Ok, let's change the first line to

    System.Data.DataTable table1 = new System.Data.DataTable("WTF");

    2. After change in (1) I get an exception System.UriFormatException "Invalid URI: URI-string is too long". Hm.. Strange. Ah! I completely confused the ReadXML method! ReadXML(string) needs a _filename_ where it can find the XML data, not the string with XML data (pretty dubious design IMHO). Ok, let's look into documentation of ReadXML() what other possibilities I actually have.

    http://msdn2.microsoft.com/en-US/library/fs0z9zxd.aspx

    "To read both data and schema, use one of the ReadXML overloads that includes the XmlReadMode parameter, and set its value to ReadSchema."

    What does MS mean here? There are NO overloads for DataTable.ReadXML() which have XmlReadMode as a parameter!

    3. The solution is "Create an extra StringReader for ReadXML() even if you have the string in memory".

    System.IO.StringReader stringReader = new System.IO.StringReader(stringWriter.ToString());

    DataTable anotherTable = new DataTable();

    anotherTable.ReadXml(stringReader);

     

    Comments are very welcome :)

    Wednesday, May 03, 2006 8:49 AM

Answers

  • You need to have the same TableName when using DataTable.ReadXml otherwise it doesn't believe the data in the Xml should belong in your DataTable.

     

     

     

    Monday, November 03, 2008 5:33 PM

All replies

  • I've just found more interesting features of "new wonderful XML functionality of DataTable".

    1. Imagine you want to pass a DataTable (serializing it) and keep the information about rows states (added/modified/unchanged etc.). You have taken a look into output of DataTable.WriteXML() with Data.XmlWriteMode.WriteSchema as parameter and checked that there is no information about row states available.

    The output with XmlWriteMode = WriteSchema looks like this

    <NewDataSet>\r\n  <xs:schema id=\"NewDataSet\" xmlns=\"\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\" xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\">\r\n    <xs:element name=\"NewDataSet\" msdata:IsDataSet=\"true\" msdata:MainDataTable=\"WTF\" msdata:UseCurrentLocale=\"true\">\r\n      <xs:complexType>\r\n        <xs:choice minOccurs=\"0\" maxOccurs=\"unbounded\">\r\n          <xs:element name=\"WTF\">\r\n            <xs:complexType>\r\n              <xs:sequence>\r\n                <xs:element name=\"Column1\" type=\"xs:int\" minOccurs=\"0\" />\r\n              </xs:sequence>\r\n            </xs:complexType>\r\n          </xs:element>\r\n        </xs:choice>\r\n      </xs:complexType>\r\n    </xs:element>\r\n  </xs:schema>\r\n  <WTF>\r\n    <Column1>0</Column1>\r\n  </WTF>\r\n  <WTF>\r\n    <Column1>1</Column1>\r\n  </WTF>\r\n</NewDataSet>

    Your first thought: No problem! Let's use XmlWriteMode = DiffGram.

    Wrong!

    Sample code:

    ...........................

    System.Data.DataTable table1 = new System.Data.DataTable("WTF");

    table1.Columns.Add("Column1", typeof(int));

    for (int i = 0; i < 10; i++)

    {

    table1.Rows.Add(i);

    }

    System.IO.StringWriter stringWriter = new System.IO.StringWriter();

    table1.WriteXml(stringWriter, System.Data.XmlWriteMode.DiffGram, false);

    stringWriter.Flush(); //Just for a case

    System.IO.StringReader stringReader = new System.IO.StringReader(stringWriter.ToString());

    DataTable anotherTable = new DataTable();

    anotherTable.ReadXml(stringReader);

    ...........................

    Result: System.InvalidOperationException "DataTable doesn't support XML-Schema Deduction"

     

    2. We use our implementation of DataColumns (inherit from System.Data.DataColumn) with some specific type-conversion info. Therefore, we need to transfer this information and on deserialization reconstruct the schema with right columns (our columns, not System.Data.DataColumns). No problem! Let's use DataTable.WriteXML() with XmlWriteMode.IgnoreSchema.

    Wrong!

    Sample code:

    ...........................

    System.Data.DataTable table1 = new System.Data.DataTable("WTF");

    table1.Columns.Add("Column1", typeof(int));

    for (int i = 0; i < 10; i++)

    {

    table1.Rows.Add(i);

    }

    System.IO.StringWriter stringWriter = new System.IO.StringWriter();

    table1.WriteXml(stringWriter, System.Data.XmlWriteMode.IgnoreSchema, false);

    stringWriter.Flush(); //Just for a case

    System.IO.StringReader stringReader = new System.IO.StringReader(stringWriter.ToString());

    DataTable anotherTable = new DataTable();

    anotherTable.Columns.Add("Column1", typeof(int)); //Reconstruct the schema

    anotherTable.ReadXml(stringReader); //No effect

    ...........................

    Well, the written XML looks pretty much ok. But it will not be loaded. No exceptions, nothing, the anotherTable is simply empty.

    I'm feeling myself as if I've payed Microsoft for being their NUnit environment.

    Wednesday, May 03, 2006 12:25 PM
  • I am having the same issue with populating my data table.  The datatable.ReadXml(stringReader) method does not load the data.  It does move the pointer in the stringReader to the end, but the table is left empty.  And same as you, no exception is thrown or anything.

     

    Can anyone help?

     

    Dim sBuf As String = cliUtils.getFiles("/admin", "*.*") ' Returns my xml string data

    Dim oTable As New DataTable, oReader As New IO.StringReader(sBuf)

    oTable.Columns.Add("Filename")

    oTable.Columns.Add("Extension")

    oTable.Columns.Add("Size")

    oTable.ReadXml(oReader)

    Thursday, April 05, 2007 11:03 PM
  • ROFL!! I just figured it out for me.. when I write the xml out, I have it writing the schema.  The table.TableName you are writing from must be the same table.TableName you are reading into.  That fixed the issue for me.
    Thursday, April 05, 2007 11:06 PM
  • 2 years x months later and I have the same problem.

     

    No mater what I do I can't POPULATE the new datatable. I can set the columns etc but get the rows - Nope.

     

    Is there an answer to all this.

    Friday, October 31, 2008 8:30 AM
  • You need to have the same TableName when using DataTable.ReadXml otherwise it doesn't believe the data in the Xml should belong in your DataTable.

     

     

     

    Monday, November 03, 2008 5:33 PM
  • This solution helped me a little. Although I now am getting back blank rows (the correct# of rows from xml file btw). I even read the schema before calling readxml.  What am I doing incorrectly?
    Tuesday, December 02, 2008 4:08 PM
  • I've been having a problem with readxml and I thought id post what I found here in case it helps anyone later.

    I had a dataset with a table in it.  I called writexml to write the data from the table.

    Later, I instantiated an instance of the table type and tried to call readxml to read in the data.  Its does not work.

    This is because the original table that saved the data was in a dataset, and the table I'm trying to read into is not because I instantiated it with the table type directly.

    If you instantiate another dataset and then call readxml from the child table whithin the dataset it will work.

    You can compare the output of writexml between a table in a dataset with a directly instantiated one, the xml from the table in a dataset has an additional set of tags naming the dataset.

    This wasted a lot of time for me this morning and now I have to figure out a way around this.

    I hope I've helped someone later on.




    Tuesday, December 09, 2008 3:40 PM
  • It sounds like you and I had the same problem.

    My main reasons for getting the blank rows in my post above was that the columnnames did not match the child node names.

    Wow, it's sure a lot easier to just use a dataset and let it auto-infer your tablename and column names and stuff. But at least now I know how to dynamically create the datatable with the correct info. 
    =========================================================================

    Does anyone know how to "append" with writeXML instead of replacing the whole file?  I have a datatable linked to the xml file. When I make my changes and then call writexml it replaces the whole entire file.  My root level node becomes <documentElement> instead of <parameters>, and also all of my comments at the top of the xml file are gone.  It seems like a ridiculous amount of work to compare each node to the datatable value.  I read about making a diffgram, but I dont understand how that will help because it wont contain but selected chunks of the data.
    Tuesday, December 09, 2008 3:53 PM
  • If anyone cares, I solved my problem by calling Clone() on the DataTable in the DataSet, then merging the cloned DataTable with the original to make a copy of it that has no DataSet reference.  then I called writexml on the cloned datatable to output xml that has no reference to a dataset.

    then when i read it back in, it has the correct columns and everything and i can easily merge the read xml into the table i want to read it into.
    Tuesday, December 09, 2008 3:58 PM
  • Thanks for your reply, it helped me alot and saved more research time

     

    Sunday, May 16, 2010 8:04 AM
  • Hi,

     

    I have the same problem... I solve it like this:

     

    On Export side...

    DataTable DT = DataSet.MyTable.Clone();
            DT = DataSet.MyTable.Copy();
            DT.TableName = "MyTable2";//This is table name on Import side
            DT.WriteXml(Application.StartupPath + "\\MyXMLFile.xml");
    

    On Import side...

    DataTable DT = DataSet.MyTable2.Clone();
            openFileDialog.ShowDialog();
            DT.ReadXml(openFileDialog.FileName);
            DataSet.MyTable2.ImportRow(DT.Rows[0]);
    
    I need to import only one row but point is that you need temp table to reed XML and you need to import rows from temp table.

    Monday, February 28, 2011 5:21 PM