Xml populate datatable

Question

I'm working on creating a DataTable from a given xml file. I've looked at other resources and similar questions but still get stuck in the same place. I want to populate a table based on my xml input file so it can look something like this:

I have got it to the point where I insert the DataColumns correctly and the no. of rows are based on the number of row_no My issue arises when trying to add the values from the elements in bomrow I'm not sure how I would populate these rows, I keep getting just one column either separated in the column sections or row sections. Heres my code so far:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
class Program
{
    static IEnumerable<XElement> headerLabels(string xmlFile)
    {
        using (XmlReader reader = XmlReader.Create(xmlFile))
        {
            reader.MoveToContent();

            while (!reader.EOF)
            {
                if (reader.NodeType == XmlNodeType.Element && reader.Name == "bomcol")
                {
                    XElement el = XElement.ReadFrom(reader) as XElement;
                    if (el != null)
                        yield return el;
                }
                else
                    reader.Read();
            }
        }
    }
    static IEnumerable<XElement> rowValues(string xmlFile)
    {
        using (XmlReader reader = XmlReader.Create(xmlFile))
        {
            reader.MoveToContent();

            while (!reader.EOF)
            {
                if (reader.NodeType == XmlNodeType.Element && reader.Name == "bomcell")
                {
                    XElement el = XElement.ReadFrom(reader) as XElement;
                    if (el != null)
                        yield return el;
                }
                else
                    reader.Read();
            }
        }
    }
    static IEnumerable<XElement> rowNums(string xmlFile)
    {
        using (XmlReader reader = XmlReader.Create(xmlFile))
        {
            reader.MoveToContent();

            while (!reader.EOF)
            {
                if (reader.NodeType == XmlNodeType.Element && reader.Name == "bomrow")
                {
                    XElement el = XElement.ReadFrom(reader) as XElement;
                    if (el != null)
                        yield return el;
                }
                else
                    reader.Read();
            }
        }
    }    
    static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        string xmlFile = @"new.xml";

        for (int i = 0; i <= 4; i++) 
        {
            IEnumerable<String> colHeaders =
              from el in headerLabels(xmlFile)
              where (int)el.Attribute("col_no") == i
              select (String)el.Attribute("name");

            foreach (String header in colHeaders)
            {
                dt.Columns.Add(header).ToString();
            }
        }


        Console.WriteLine("Rows: " + dt.Rows.Count);
        Console.WriteLine("Cols: " + dt.Columns.Count);
        DataColumnCollection cols = dt.Columns;
        foreach (DataColumn col in cols)
        {
            Console.Write(col.ColumnName + "\t");
        }
        Console.WriteLine();
        IEnumerable<String> rows =
              from el in rowNums(xmlFile)
              where (int)el.Attribute("row_no") >= 0
              select (String)el.Attribute("row_no");
        foreach (String row_n in rows)
        {
            DataRow rws = dt.Rows.Add(); //.Add(DATA Values)
        }
        List<string> rowVals = new List<string>();
        foreach (DataRow dtRow in dt.Rows)
        {

        }
        Console.WriteLine("Rows: " + dt.Rows.Count);
        Console.WriteLine("Cols: " + dt.Columns.Count);
        Console.WriteLine();
        Console.ReadLine();

    }
}

Here is the sample xml I'm using:

<xml>
    <transactions>
        <transaction>
            <bom>
            <bomheader>
                <bomcol alignment="center" col_no="0" name="ITEM NO."/>
                <bomcol alignment="center" col_no="1" name="ITEMCODE"/>
                <bomcol alignment="center" col_no="2" name="PARTNUMBER"/>
                <bomcol alignment="center" col_no="3" name="DESCRIPTION"/>
                <bomcol alignment="center" col_no="4" name=" QTY."/>
            </bomheader>
                <bomrow document_id="32" path="\PARTS" row_no="0">
                    <bomcell col_no="0" value="1"/>
                    <bomcell col_no="1" value="201"/>
                    <bomcell col_no="2" value="75"/>
                    <bomcell col_no="3" value="MEMBER"/>
                    <bomcell col_no="4" value="2"/>
                </bomrow>
                <bomrow document_id="35" path="\PARTS" row_no="1">
                    <bomcell col_no="0" value="2"/>
                    <bomcell col_no="1" value="205"/>
                    <bomcell col_no="2" value="75-LH"/>
                    <bomcell col_no="3" value="MEMBER LEFT HAND"/>
                    <bomcell col_no="4" value="1"/>
                </bomrow>
                <bomrow document_id="30" path="\PARTS" row_no="2">
                    <bomcell col_no="0" value="3"/>
                    <bomcell col_no="1" value="200"/>
                    <bomcell col_no="2" value="01AB"/>
                    <bomcell col_no="3" value="FRAME"/>
                    <bomcell col_no="4" value="1"/>
                </bomrow>
            </bom>
        </transaction>
    </transactions>
</xml>

All help (or resources) is appreciated!

Answer 1

Building from jdweng's answer:

foreach(XElement bomheader in doc.Descendants("bomheader"))
        {
            dt.Columns.Add(
                bomheader.Elements("bomcol").Where(x => (int)x.Attribute("col_no") == 0).FirstOrDefault() == null ?
                   null : "ITEM NO."
                    );
            dt.Columns.Add(
                bomheader.Elements("bomcol").Where(x => (int)x.Attribute("col_no") == 1).FirstOrDefault() == null ?
                   null : "ITEMCODE"
                );
            dt.Columns.Add(
                bomheader.Elements("bomcol").Where(x => (int)x.Attribute("col_no") == 2).FirstOrDefault() == null ?
                    null : "PARTNUMBER"
                );
            dt.Columns.Add(
                bomheader.Elements("bomcol").Where(x => (int)x.Attribute("col_no") == 3).FirstOrDefault() == null ?
                    null : "DESCRIPTION"
                );
            dt.Columns.Add(
                bomheader.Elements("bomcol").Where(x => (int)x.Attribute("col_no") == 4).FirstOrDefault() == null ?
                    null : "QTY."
                    );
        }

This will ensure no matter the input (or lack of input) for bomcol values the table columns will be correctly set to default values. This also takes care of having columns out of order since the col_no is the only crucial & reliable info.

Answer 2

Try xml linq :

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

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

            dt.Columns.Add("ITEM NO.", typeof(int));
            dt.Columns.Add("ITEMCODE", typeof(string));
            dt.Columns.Add("PARTNUMBER.", typeof(string));
            dt.Columns.Add("DESCRIPTION", typeof(string));
            dt.Columns.Add("QTY.", typeof(int));

            XDocument doc = XDocument.Load(FILENAME);

            foreach (XElement bomrow in doc.Descendants("bomrow"))
            {
                dt.Rows.Add(new object[] {
                    bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 0).FirstOrDefault() == null ?
                        null : (int?)bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 0).FirstOrDefault().Attribute("value"),
                    bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 1).FirstOrDefault() == null ?
                        null : (string)bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 1).FirstOrDefault().Attribute("value"),
                    bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 2).FirstOrDefault() == null ?
                        null : (string)bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 2).FirstOrDefault().Attribute("value"),
                    bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 3).FirstOrDefault() == null ?
                        null : (string)bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 3).FirstOrDefault().Attribute("value"),
                    bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 4).FirstOrDefault() == null ?
                       null : (int?)bomrow.Elements("bomcell").Where(x => (int)x.Attribute("col_no") == 4).FirstOrDefault().Attribute("value")
                });
            }
        }
    }
}

Answer 3

You can resolve your issue with DataSet like this (It's not a beautiful solution but it works ;) :

 static void Main(string[] args)
    {
        DataTable dt = new DataTable("Items");
        string xmlFile = @"new.xml";

        DataSet ds = new DataSet();
        ds.ReadXml(xmlFile);

        foreach (DataRow rowCol in ds.Tables["bomcol"].Rows)
        {
            dt.Columns.Add(rowCol.ItemArray[2].ToString());
        }

        DataRow dr = dt.Rows.Add();
        for (int j = 0; j < ds.Tables["bomcell"].Rows.Count; j++)
        {

            var i = j % 5;
            if (i == 0 && j != 0)
            {
                dr = dt.Rows.Add();
            }

            dr[dt.Columns[i]] = ds.Tables["bomcell"].Rows[j].ItemArray[1];
        }

        Console.WriteLine("Rows: " + dt.Rows.Count);
        Console.WriteLine("Cols: " + dt.Columns.Count);
        DataColumnCollection cols = dt.Columns;
        foreach (DataColumn col in cols)
        {
            Console.Write(cols[0] + "\t");
        }

        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine();
            Console.Write(row.ItemArray[0] + "\t\t" + row.ItemArray[1] + "\t\t" + row.ItemArray[2] + "\t\t" + row.ItemArray[3] + "\t\t" + row.ItemArray[4] + "\t");
        }

        Console.ReadLine();
    }