Convert DataTable into XML hierarchy in C#

Question

I have the following DataTable:

A   B   C
----------
A1  B1  C1
A1  B1  C2
A1  B1  C3
A1  B2  C1
A1  B2  C2
----------

I tried to convert it using C# to be in XML format like this:

<Data>
    <A>
        <lable>A1</lable>
        <B>
            <lable>B1</lable>
            <C>
                <lable>C1</lable>
                <lable>C2</lable>
                <lable>C3</lable>
            </C>
            <lable>B2</lable>
            <C>
                <lable>C1</lable>
                <lable>C2</lable>
            </C>
        </B>
    </A>
</Data>

I did a deep search and I found something helpful online like this. But that one uses relations, and my data is just one table. I also tried this C# code:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Xml.Linq;
    using System.Data.SqlClient;

    SqlConnection con = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Files;Data Source=localhost");
    con.Open();
    SqlCommand cmd = new SqlCommand("select * from MyTable",con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable DT = new DataTable("Data");
    da.Fill(DT);
    dataGridView1.DataSource = DT;
    con.Close();
    string XML = ToXmlFormat(DT, 0);
    Console.WriteLine(XML);


public string ToXmlFormat(this DataTable table, int metaIndex = 0)
{
    XDocument xdoc = new XDocument(
        new XElement(table.TableName,
            from column in table.Columns.Cast<DataColumn>()
            where column != table.Columns[metaIndex]
            select new XElement(column.ColumnName,
                from row in table.AsEnumerable()
                select new XElement(row.Field<string>(metaIndex), row[column])
                )
            )
        );

    return xdoc.ToString();
}

The result was another format which is not nested XML as I explained. see image

How can I convert my table into the XML format I need?

Answer 1

You need to use GroupBy to group the rows, then select the parts you want into XElements.
The code below should do what you want:

    var xml = new XElement(table.TableName, table.Rows.Cast<DataRow>()
        .GroupBy(row => (string)row[0])
        .Select(g =>
            new XElement(table.Columns[0].ColumnName,
                new XElement("label", g.Key),
                g.GroupBy(row => (string)row[1])
                 .Select(g1 =>
                    new XElement(table.Columns[1].ColumnName,
                        new XElement("label", g1.Key),
                        new XElement(table.Columns[2].ColumnName,
                            g1.Select(row =>
                                new XElement("label", (string)row[2])
                            )
                        )
                    )
                )
            )
        )
    ).ToString();

Fiddle: https://dotnetfiddle.net/qEWNvj