LINQ algorithm to create generic table from XML

Question

Here is a piece of code:

XNamespace z = "#SomeSchema";
var listCols = new HashSet<Col>();
var colNameList = new List<string>(..some values..);

var xElementList = doc.Descendants(z + "row");

return new HashSet<Row>(xElementList .Select(x=> new Row
       {
         Col= new List<Col>(listCols).Select(col =>
         {
           col.Value= (string)x.Attribute(colNameList.First(colName=> colName == col.Name));
           return col;
         }).ToList()
       }));

What is wrong is that, the return value contains a List of Row, but all of these rows have the exact same value (for the Col Value).

Ex, Row[1].Col[1].Value == Row[2].Col[2].Value

And these values should be completly different. I am obtaining those values from an Xml file. When I debug the xElementList, values are différents, but when I try to create rows with them, all rows are the same. Actually, the Rows have the same Columns list, which is the last record of the xElementList.

Am I doing something wrong?

Thank you.

Answer 1

See code below. I read the xml twice. Once to get the columns names and add columns to table. Then read xml 2nd time to get row data.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.IO;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();

            StreamReader sReader = new StreamReader(FILENAME, Encoding.GetEncoding(1252));

            XmlReader reader = XmlReader.Create(sReader);
            Dictionary<string, string> colDict = new Dictionary<string, string>();
            while (!reader.EOF)
            {
                if (reader.Name != "FIELD")
                {
                    reader.ReadToFollowing("FIELD");
                }
                if (!reader.EOF)
                {
                    XElement field = (XElement)XElement.ReadFrom(reader);
                    string attrname = (string)field.Attribute("attrname");
                    string fieldtype = (string)field.Attribute("fieldtype");
                    switch (fieldtype)
                    {
                        case "string":
                            dt.Columns.Add(attrname, typeof(string));
                            break;
                        case "i4":
                            dt.Columns.Add(attrname, typeof(int));
                            break;
                    }
                    colDict.Add(attrname, fieldtype);
                }
            }
            reader.Close();
            sReader = new StreamReader(FILENAME, Encoding.GetEncoding(1252));
            reader = XmlReader.Create(sReader);
            while (!reader.EOF)
            {
                if (reader.Name != "ROW")
                {
                    reader.ReadToFollowing("ROW");
                }
                if (!reader.EOF)
                {
                    XElement row = (XElement)XElement.ReadFrom(reader);
                    DataRow newRow = dt.Rows.Add();
                    foreach (XAttribute attrib in row.Attributes())
                    {
                        string colName = attrib.Name.LocalName;
                        if (colDict.ContainsKey(colName))
                        {
                            switch (colDict[colName])
                            {
                                case "string":
                                    newRow[colName] = (string)attrib;
                                    break;
                                case "i4":
                                    newRow[colName] = (int)attrib;
                                    break;
                            }
                        }
                    }
                }
            }
        }
    }
}