title: XML to DataTable - returning blank cells
body: <p>This is going to be a lengthy question - I have been spending all day trying to solve this issue so perhaps you guys could help! I am trying to future-proof my database application by allowing the database structure to be changed when it is presented with a new structure (in the format of XML).</p>
  
  <p>Currently I am managing to export the database structure into XML just fine. See working code below:</p>
  
  <pre><code>    public void generateXMLStructureCompactDB(string fileName)
      {
          DataTable table = new DataTable();
          int position;
          FileStream fsWrite;
          StreamWriter sw;
  
          string version = "1.1.1.0";
          table.Columns.Add("TableName", Type.GetType("System.String"));
          table.Columns.Add("ColName", Type.GetType("System.String"));
          table.Columns.Add("Position", Type.GetType("System.Int32"));
          table.Columns.Add("DataType", Type.GetType("System.String"));
          table.Columns.Add("MaximumLength", Type.GetType("System.Int32"));
          table.Columns.Add("Precision", Type.GetType("System.Int32"));
          table.Columns.Add("Scale", Type.GetType("System.Int32"));
          table.Columns.Add("Nullable", Type.GetType("System.Boolean"));
          table.Columns.Add("Identity", Type.GetType("System.Boolean"));
          table.Columns.Add("IdentitySeed", Type.GetType("System.Int32"));
          table.Columns.Add("IdentityIncrement", Type.GetType("System.Int32"));
  
          string sql = "select Table_Name, Column_Name, Ordinal_Position, Data_Type, " +
              "Character_Maximum_Length, Numeric_Precision, Numeric_Scale, Is_Nullable, " +
              "case when AutoInc_Seed is null then 0 else 1 end as IS_IDENTITY, " +
              "AutoInc_Seed, AutoInc_Increment from information_schema.columns";
          runSQL(sql, out table);
  
          if (File.Exists(fileName))
              File.Delete(fileName);
  
          fsWrite = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);
          sw = new StreamWriter(fsWrite, Encoding.ASCII);
  
          XmlWriter writer = new XmlTextWriter(sw);
          writer.WriteStartDocument(false);
          writer.WriteRaw(Environment.NewLine);
          writer.WriteComment("DBVersion=" + DBVersion + ", created=" + DateTime.Now.ToString());
          writer.WriteRaw(Environment.NewLine);
  
          writer.Close();
          sw.Close();
          fsWrite.Close();
  
          fsWrite = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.ReadWrite);
          sw = new StreamWriter(fsWrite, Encoding.ASCII);
  
          DataTable indexes = new DataTable();
          indexes.Columns.Add("TableName", Type.GetType("System.String"));
          indexes.Columns.Add("Schema", Type.GetType("System.String"));
          indexes.Columns.Add("IndexName", Type.GetType("System.String"));
          indexes.Columns.Add("Clustered", Type.GetType("System.Boolean"));
          indexes.Columns.Add("Unique", Type.GetType("System.Boolean"));
          indexes.Columns.Add("IndexColName", Type.GetType("System.String"));
          sql = "select Table_Name, Table_Schema, Index_Name, [Clustered], [Unique], " + 
              "Column_Name from information_schema.indexes";
          runSQL(sql, out indexes);
  
          table.TableName = "Tables";
          indexes.TableName = "Indexes";
  
          table.WriteXml(sw);
          sw.WriteLine("");
          indexes.WriteXml(sw);
          sw.Close();
          fsWrite.Close();
      }
  </code></pre>
  
  <p>Below is my generated XML file (actual version is way bigger, but I've stripped it down to a single table and a single index):</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="us-ascii" standalone="no"?&gt;
  &lt;!--DBVersion=1.0.0.0, created=14/03/2013 13:18:20--&gt;
  &lt;DocumentElement&gt;
    &lt;Tables&gt;
      &lt;TABLE_NAME&gt;ContactLog&lt;/TABLE_NAME&gt;
      &lt;COLUMN_NAME&gt;ContactLogId&lt;/COLUMN_NAME&gt;
      &lt;ORDINAL_POSITION&gt;1&lt;/ORDINAL_POSITION&gt;
      &lt;DATA_TYPE&gt;int&lt;/DATA_TYPE&gt;
      &lt;NUMERIC_PRECISION&gt;10&lt;/NUMERIC_PRECISION&gt;
      &lt;IS_NULLABLE&gt;NO&lt;/IS_NULLABLE&gt;
      &lt;IS_IDENTITY&gt;1&lt;/IS_IDENTITY&gt;
      &lt;AUTOINC_SEED&gt;1&lt;/AUTOINC_SEED&gt;
      &lt;AUTOINC_INCREMENT&gt;1&lt;/AUTOINC_INCREMENT&gt;
    &lt;/Tables&gt;
    &lt;Tables&gt;
      &lt;TABLE_NAME&gt;ContactLog&lt;/TABLE_NAME&gt;
      &lt;COLUMN_NAME&gt;CustomerId&lt;/COLUMN_NAME&gt;
      &lt;ORDINAL_POSITION&gt;2&lt;/ORDINAL_POSITION&gt;
      &lt;DATA_TYPE&gt;int&lt;/DATA_TYPE&gt;
      &lt;NUMERIC_PRECISION&gt;10&lt;/NUMERIC_PRECISION&gt;
      &lt;IS_NULLABLE&gt;YES&lt;/IS_NULLABLE&gt;
      &lt;IS_IDENTITY&gt;0&lt;/IS_IDENTITY&gt;
    &lt;/Tables&gt;
    &lt;Tables&gt;
      &lt;TABLE_NAME&gt;ContactLog&lt;/TABLE_NAME&gt;
      &lt;COLUMN_NAME&gt;ContactDate&lt;/COLUMN_NAME&gt;
      &lt;ORDINAL_POSITION&gt;3&lt;/ORDINAL_POSITION&gt;
      &lt;DATA_TYPE&gt;datetime&lt;/DATA_TYPE&gt;
      &lt;NUMERIC_PRECISION&gt;23&lt;/NUMERIC_PRECISION&gt;
      &lt;NUMERIC_SCALE&gt;3&lt;/NUMERIC_SCALE&gt;
      &lt;IS_NULLABLE&gt;YES&lt;/IS_NULLABLE&gt;
      &lt;IS_IDENTITY&gt;0&lt;/IS_IDENTITY&gt;
    &lt;/Tables&gt;
    &lt;Tables&gt;
      &lt;TABLE_NAME&gt;ContactLog&lt;/TABLE_NAME&gt;
      &lt;COLUMN_NAME&gt;ContactTypeId&lt;/COLUMN_NAME&gt;
      &lt;ORDINAL_POSITION&gt;4&lt;/ORDINAL_POSITION&gt;
      &lt;DATA_TYPE&gt;int&lt;/DATA_TYPE&gt;
      &lt;NUMERIC_PRECISION&gt;10&lt;/NUMERIC_PRECISION&gt;
      &lt;IS_NULLABLE&gt;YES&lt;/IS_NULLABLE&gt;
      &lt;IS_IDENTITY&gt;0&lt;/IS_IDENTITY&gt;
    &lt;/Tables&gt;
    &lt;Tables&gt;
      &lt;TABLE_NAME&gt;ContactLog&lt;/TABLE_NAME&gt;
      &lt;COLUMN_NAME&gt;AuditText&lt;/COLUMN_NAME&gt;
      &lt;ORDINAL_POSITION&gt;5&lt;/ORDINAL_POSITION&gt;
      &lt;DATA_TYPE&gt;nvarchar&lt;/DATA_TYPE&gt;
      &lt;CHARACTER_MAXIMUM_LENGTH&gt;2048&lt;/CHARACTER_MAXIMUM_LENGTH&gt;
      &lt;IS_NULLABLE&gt;YES&lt;/IS_NULLABLE&gt;
      &lt;IS_IDENTITY&gt;0&lt;/IS_IDENTITY&gt;
    &lt;/Tables&gt;
    &lt;Tables&gt;
      &lt;TABLE_NAME&gt;ContactType&lt;/TABLE_NAME&gt;
      &lt;COLUMN_NAME&gt;ContactTypeId&lt;/COLUMN_NAME&gt;
      &lt;ORDINAL_POSITION&gt;1&lt;/ORDINAL_POSITION&gt;
      &lt;DATA_TYPE&gt;int&lt;/DATA_TYPE&gt;
      &lt;NUMERIC_PRECISION&gt;10&lt;/NUMERIC_PRECISION&gt;
      &lt;IS_NULLABLE&gt;NO&lt;/IS_NULLABLE&gt;
      &lt;IS_IDENTITY&gt;1&lt;/IS_IDENTITY&gt;
      &lt;AUTOINC_SEED&gt;1&lt;/AUTOINC_SEED&gt;
      &lt;AUTOINC_INCREMENT&gt;1&lt;/AUTOINC_INCREMENT&gt;
    &lt;/Tables&gt;
  &lt;/DocumentElement&gt;
  &lt;DocumentElement&gt;
    &lt;Indexes&gt;
      &lt;TABLE_NAME&gt;ContactLog&lt;/TABLE_NAME&gt;
      &lt;INDEX_NAME&gt;PK_ContactLog&lt;/INDEX_NAME&gt;
      &lt;CLUSTERED&gt;false&lt;/CLUSTERED&gt;
      &lt;UNIQUE&gt;true&lt;/UNIQUE&gt;
      &lt;COLUMN_NAME&gt;ContactLogId&lt;/COLUMN_NAME&gt;
    &lt;/Indexes&gt;
  &lt;/DocumentElement&gt;
  </code></pre>
  
  <p>Now this is the part of the code that reads the XML file. The XML file contains the columns of two seperate tables - <code>tables</code> (which is actually a list of columns), and <code>indexes</code> - a list of indexes. It splits the file into two based on the position of <code>&lt;DocumentElement&gt;</code>, then puts the two XML elements into two separate tables. See code below:</p>
  
  <pre><code>    static bool VerifyStructure()
      {
          FileStream fsWrite;
          int i;
          StreamWriter sw;
          string DBStructureVersion = "0.0.0.0";
          string xmlFile;
          string s;
          string tmp = Environment.GetEnvironmentVariable("TEMP");
          if (tmp == "")
              tmp = Environment.GetEnvironmentVariable("SystemDrive");
          try
          {
              xmlFile = tmp + @"\dbStructure.xml";
              if (File.Exists(xmlFile))
                  File.Delete(xmlFile);
  
              fsWrite = new FileStream(xmlFile, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);
              sw = new StreamWriter(fsWrite, Encoding.ASCII);
              sw.Write(syntos.Properties.Resources.dbStructure);
              sw.Close();
              fsWrite.Close();
          }
          catch (Exception ex)
          {
              MessageBox.Show("Error reading structure xml file: " + ex.Message);
              return false;
          }
          if (!File.Exists(xmlFile))
          {
              MessageBox.Show("XML Structure file '" + xmlFile + "' does not exist.");
              return false;
          }
          StreamReader sr = new StreamReader(xmlFile);
          s = sr.ReadLine();
          while (s != null)
          {
              if (s.Contains("DBVersion="))
              {
                  i = s.IndexOf("DBVersion=");
                  DBStructureVersion = s.Substring(i + 10);
                  i = DBStructureVersion.IndexOf(",");
                  DBStructureVersion = DBStructureVersion.Substring(0, i);
                  break;
              }
              s = sr.ReadLine();
          }
          sr.Close();
  
          // Split the remaining XML file into two: table datatable file and index datatable file
          // Write out to file1 all the table information and to file2 all the index information
          string file1path;
          string file2path;
  
          try
          {
              file1path = tmp + @"\tables.xml";
              file2path = tmp + @"\indexes.xml";
              if (File.Exists(file1path))
                  File.Delete(file1path);
  
              if (File.Exists(file2path))
                  File.Delete(file2path);
  
              FileStream fsw1 = new FileStream(file1path, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);
              FileStream fsw2 = new FileStream(file2path, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite);
              StreamWriter sw1 = new StreamWriter(fsw1, Encoding.ASCII);
              StreamWriter sw2 = new StreamWriter(fsw2, Encoding.ASCII);
  
              // Create a file containing just the table / column definitions
              sr = new StreamReader(xmlFile);
              s = sr.ReadToEnd();
              sr.Close();
  
              i = s.IndexOf("&lt;DocumentElement&gt;");
              s = s.Substring(i);
              i = s.IndexOf("&lt;/DocumentElement&gt;");
              s = s.Substring(0, i + 18);
              sw1.Write(s);
              sw1.Close();
              fsw1.Close();
  
              // Create a file containing the index column definitions
              sr = new StreamReader(xmlFile);
              s = sr.ReadToEnd();
              sr.Close();
  
              i = s.IndexOf("&lt;/DocumentElement&gt;");
              s = s.Substring(i + 18);
              sw2.Write(s);
              sw2.Close();
              fsw2.Close();
              sr.Close();
          }
          catch (Exception ex)
          {
              MessageBox.Show("Error producing the table and index XML file. " + ex.Message);
              return false;
          }
  
          DataTable table = new DataTable();
          table.Columns.Add("TableName", Type.GetType("System.String"));
          table.Columns.Add("ColName", Type.GetType("System.String"));
          table.Columns.Add("Position", Type.GetType("System.Int32"));
          table.Columns.Add("DataType", Type.GetType("System.String"));
          table.Columns.Add("MaximumLength", Type.GetType("System.Int32"));
          table.Columns.Add("Precision", Type.GetType("System.Int32"));
          table.Columns.Add("Scale", Type.GetType("System.Int32"));
          table.Columns.Add("Nullable", Type.GetType("System.Boolean"));
          table.Columns.Add("Identity", Type.GetType("System.Boolean"));
          table.Columns.Add("IdentitySeed", Type.GetType("System.Int32"));
          table.Columns.Add("IdentityIncrement", Type.GetType("System.Int32"));
          table.TableName = "Tables";
  
          DataTable indexes = new DataTable();
          indexes.Columns.Add("TableName", Type.GetType("System.String"));
          indexes.Columns.Add("Schema", Type.GetType("System.String"));
          indexes.Columns.Add("IndexName", Type.GetType("System.String"));
          indexes.Columns.Add("Clustered", Type.GetType("System.Boolean"));
          indexes.Columns.Add("Unique", Type.GetType("System.Boolean"));
          indexes.Columns.Add("IndexColName", Type.GetType("System.String"));
          indexes.TableName = "Indexes";
  
          // Read in the XML for the table / columns
          sr = new StreamReader(file1path);
          table.ReadXml(sr);
          sr.Close();
          if (table.Rows.Count == 0)
          {
              MessageBox.Show("No loadable table rows found in XML file");
              sw.Close();
              fsWrite.Close();
              return false;
          }
          sr = new StreamReader(file2path);
          indexes.ReadXml(sr);
          sr.Close();
          if (indexes.Rows.Count == 0)
          {
              MessageBox.Show("No loadable index rows found in XML file");
              sw.Close();
              fsWrite.Close();
              return false;
          }
          DbFunctions.BackupDatabase();
          bool success1 = CreateTempTables(table);
          return true;
      }
  </code></pre>
  
  <p>Through the use of breakpoints and debug statements, I can see that the tables are being populated with the correct number of rows and columns - however all of these are null. Just empty cells. I have used to <code>table.Rows.Count</code> to see that there are actually rows in this table.</p>
  
  <p>If anyone could perhaps see if I'm on the right lines here, I would be eternally grateful! Thanks in advance :)</p>
  
  <p>Edit:
  I have tested this with a different XML sheet and it works. There must be something wrong with the way I am generating my XML file but I cannot see what.</p>
  
  <p>I shall be adding a bounty to this question as soon as it allows me to ;)</p>
  
  <p><strong>Summary of problem:</strong> When an XML file generated by the system (first code block) is imported into the program again (third code block), the rows and columns are present but all the cells are blank.</p>