C# How to Group List for XML Object

Question

I need help, for group by my XML object class.

Here my data from SQL server, my Query ( select * from TaxXMLdb ) =

TIN	DocEntryAR	BuyerDocNum	BuyerName	Opt	ItemCode	Name	DocEntryAR2
AAA	100100	5533	Anto	0	2001	Book	100100
AAA	100100	5533	Anto	0	2002	Desk	100100
AAA	100100	5533	Anto	0	2003	Key	100100
AAA	200100	7722	Dani	0	5001	Lamp	200100
AAA	200100	7722	Dani	0	5002	Car	200100
AAA	300100	2211	Nina	0	3001	Fan	300100

I want XML output look like this,

Group By DocEntryAR Header, for each Detail (Opt, ItemCode, Name, DocEntryAR2)

((DocEntryAR, BuyerDocNum, BuyerName) as Header)

((Opt, ItemCode, Name, DocEntryAR2) as Detail), XML output must look like this below:

<TaxInvoiceBulk>
    <TIN>AAA</TIN>
    <ListOfTaxInvoice>
        <TaxInvoice>
            <DocEntryAR>100100</DocEntryAR>
            <BuyerDocNum>5533</BuyerDocNum>
            <BuyerName>Anto</BuyerName>
            <ListOfGoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>2001</ItemCode>
                    <Name>Book</Name>
                    <DocEntryAR2>100100</DocEntryAR2>
                </GoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>2002</ItemCode>
                    <Name>Desk</Name>
                    <DocEntryAR2>100100</DocEntryAR2>
                </GoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>2003</ItemCode>
                    <Name>Key</Name>
                    <DocEntryAR2>100100</DocEntryAR2>
                </GoodService>
            </ListOfGoodService>
        </TaxInvoice>
        <TaxInvoice>
            <DocEntryAR>200100</DocEntryAR>
            <BuyerDocNum>7722</BuyerDocNum>
            <BuyerName>Dani</BuyerName>
            <ListOfGoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>5001</ItemCode>
                    <Name>Lamp</Name>
                    <DocEntryAR2>200100</DocEntryAR2>
                </GoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>5002</ItemCode>
                    <Name>Car</Name>
                    <DocEntryAR2>200100</DocEntryAR2>
                </GoodService>
            </ListOfGoodService>
        </TaxInvoice>
        <TaxInvoice>
            <DocEntryAR>300100</DocEntryAR>
            <BuyerDocNum>2211</BuyerDocNum>
            <BuyerName>Nina</BuyerName>
            <ListOfGoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>3001</ItemCode>
                    <Name>Fan</Name>
                    <DocEntryAR2>300100</DocEntryAR2>
                </GoodService>
            </ListOfGoodService>
        </TaxInvoice>
    </ListOfTaxInvoice>
</TaxInvoiceBulk>

I use that XML class List & code can Generate XML from C#

using System.Xml.Serialization;

but my output XML still Wrong result, not look like I want.

Anyone can help for Repair / Creating new List Class & Group by us LinQ, or other else solution for output XML like that. my class =

    [Serializable]
    [XmlType(TypeName = "TaxInvoiceBulk")]
    public class TaxInvoiceBulk
    {
        public string TIN { get; set; }
        [XmlArray("ListOfTaxInvoice")] // <--
        public List<TaxInvoice> TaxInvoices { get; set; }
        public TaxInvoiceBulk()
        {
            TaxInvoices = new List<TaxInvoice>();
        }

        [Serializable]
        public class TaxInvoice
        {
            public string DocEntryAR { get; set; }
            public string BuyerDocNum { get; set; }
            public string BuyerName { get; set; }
            [XmlArray("ListOfGoodService")] // <--
            public List<GoodService> GoodServices { get; set; }
            public TaxInvoice()
            {
                GoodServices = new List<GoodService>();
            }
        }

        [Serializable]
        public class GoodService
        {
            public string Opt { get; set; }
            public string ItemCode { get; set; }
            public string Name { get; set; }
            public string DocEntryAR2 { get; set; }
        }
    }

My Full Code in C# using System.Xml.Serialization;

1. Declare XML class as public

public TaxInvoiceBulk Tax123 = new TaxInvoiceBulk();

than 2. Show in DataGrid First

private void btnGrid_Click(object sender, EventArgs e)
{
    SqlConnection myConnection = KonekSQL.create_concection();
    SqlDataAdapter dataAdapter = null;
    DataTable dataTable = null;

    //////string sql = $@"SELECT * FROM TaxXMLdb";
    string sql = $@"SELECT  DISTINCT(DocEntryAR), TIN, BuyerDocNum, BuyerName FROM TaxXMLdb";

    dataAdapter = new SqlDataAdapter(sql, myConnection);
    dataTable = new DataTable();
    dataAdapter.Fill(dataTable);
    dataGridView1.DataSource = dataTable;

    dataGridView1.AllowUserToAddRows = false; ////datagridview remove empty row.
    myConnection.Close();

    fillToXmlClass();
}

3. make function to fill TaxInvoiceBulk Tax123 (fillToXmlClass()) from grid & 1 more SQL

public void fillToXmlClass()
    {
        string doken1 = null;

        SqlConnection myConnection = KonekSQL.create_concection();
        SqlDataReader myReader = null;
        SqlCommand myCommand = null;


        for (int i = 0; i < dataGridView1.Rows.Count; i += 1)
        {

            doken1 = dataGridView1.Rows[i].Cells["DocEntryAR"].Value.ToString();

            Tax123.TaxInvoices.Add(new TaxInvoice
            {
                DocEntryAR = dataGridView1.Rows[i].Cells["DocEntryAR"].Value.ToString(),
                BuyerDocNum = dataGridView1.Rows[i].Cells["BuyerDocNum"].Value.ToString(),
                BuyerName = dataGridView1.Rows[i].Cells["BuyerName"].Value.ToString()

            });

            string sql = $@"SELECT * FROM TaxXMLdb WHERE DocEntryAR = '{doken1}'";
            myCommand = new SqlCommand(sql, myConnection);
            myReader = myCommand.ExecuteReader();


            while (myReader.Read())
            {
                Tax123.TaxInvoices.FirstOrDefault().GoodServices.Add(new GoodService
                {
                    DocEntryAR2 = myReader["DocEntryAR2"].ToString(),
                    Opt = myReader["Opt"].ToString(),
                    ItemCode = myReader["ItemCode"].ToString(),
                    Name = myReader["Name"].ToString()
                });


            }//END While

        }//END Loop FOR GRID
        myConnection.Close();
}

then 4. I show it XML result using Message box

private void btnXML_Click(object sender, EventArgs e)
{
    var serialXML1 = new XmlSerializer(typeof(TaxInvoiceBulk));
    using (StringWriter textWriter1 = new StringWriter())
    {
        serialXML1.Serialize(textWriter1, Tax123);
        MessageBox.Show($@"{textWriter1.ToString()}");
    }
}

5. My result XML still wrong, I don't know how to Group GoodService, by TaxInvoice.DocEntryAR.

<?xml version="1.0"?>
<TaxInvoiceBulk
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <ListOfTaxInvoice>
        <TaxInvoice>
            <DocEntryAR>100100</DocEntryAR>
            <BuyerDocNum>5533</BuyerDocNum>
            <BuyerName>Anto</BuyerName>
            <ListOfGoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>2001</ItemCode>
                    <Name>Book</Name>
                    <DocEntryAR2>100100</DocEntryAR2>
                </GoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>2002</ItemCode>
                    <Name>Desk</Name>
                    <DocEntryAR2>100100</DocEntryAR2>
                </GoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>2003</ItemCode>
                    <Name>Key</Name>
                    <DocEntryAR2>100100</DocEntryAR2>
                </GoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>5001</ItemCode>
                    <Name>Lamp</Name>
                    <DocEntryAR2>200100</DocEntryAR2>
                </GoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>5002</ItemCode>
                    <Name>Car</Name>
                    <DocEntryAR2>200100</DocEntryAR2>
                </GoodService>
                <GoodService>
                    <Opt>0</Opt>
                    <ItemCode>3001</ItemCode>
                    <Name>Fan</Name>
                    <DocEntryAR2>300100</DocEntryAR2>
                </GoodService>
            </ListOfGoodService>
        </TaxInvoice>
        <TaxInvoice>
            <DocEntryAR>200100</DocEntryAR>
            <BuyerDocNum>7722</BuyerDocNum>
            <BuyerName>Dani</BuyerName>
            <ListOfGoodService />
        </TaxInvoice>
        <TaxInvoice>
            <DocEntryAR>300100</DocEntryAR>
            <BuyerDocNum>2211</BuyerDocNum>
            <BuyerName>Nina</BuyerName>
            <ListOfGoodService />
        </TaxInvoice>
    </ListOfTaxInvoice>
</TaxInvoiceBulk>

Answer 1

Here is a solution based on SQL Server T-SQL that is composing desired output XML in one single statement.

XML hierarchy has three levels: parent, child, and grandchild. It is visible through nested SELECT ... FOR XML ... statements with corresponding aliases.

And just call it directly from the c# program, or package it as an SQL Server stored procedure.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    TIN CHAR(3), 
    DocEntryAR CHAR(6), 
    BuyerDocNum INT, 
    BuyerName VARCHAR(20),
    Opt BIT,
    ItemCode CHAR(4),
    Name VARCHAR(30),
    DocEntryAR2 CHAR(6));
INSERT INTO @tbl VALUES
('AAA', '100100', 5533, 'Anto', 0, 2001, 'Book', 100100),
('AAA', '100100', 5533, 'Anto', 0, 2002, 'Desk', 100100),
('AAA', '100100', 5533, 'Anto', 0, 2003, 'Key',  100100),
('AAA', '200100', 7722, 'Dani', 0, 5001, 'Lamp', 200100),
('AAA', '200100', 7722, 'Dani', 0, 5002, 'Car',  200100),
('AAA', '300100', 2211, 'Nina', 0, 3001, 'Fan',  300100);
-- DDL and sample data population, end

SELECT TIN
    , (
        SELECT DocEntryAR, BuyerDocNum, BuyerName
    , (
        SELECT Opt, ItemCode, Name, DocEntryAR2
        FROM @tbl AS gc -- grandchild
        WHERE c.DocEntryAR = gc.DocEntryAR
            AND c.BuyerDocNum = gc.BuyerDocNum
            AND c.BuyerName = gc.BuyerName
        FOR XML PATH('GoodService'), TYPE, ROOT('ListOfGoodService')
    )
    FROM @tbl AS c  -- child
    WHERE p.TIN = c.TIN
    GROUP BY DocEntryAR, BuyerDocNum, BuyerName
    FOR XML PATH('TaxInvoice'), TYPE, ROOT('ListOfTaxInvoice')
)
FROM @tbl AS p -- parent
GROUP BY TIN
FOR XML PATH(''), TYPE, ROOT('TaxInvoiceBulk');

Output

<TaxInvoiceBulk>
  <TIN>AAA</TIN>
  <ListOfTaxInvoice>
    <TaxInvoice>
      <DocEntryAR>100100</DocEntryAR>
      <BuyerDocNum>5533</BuyerDocNum>
      <BuyerName>Anto</BuyerName>
      <ListOfGoodService>
        <GoodService>
          <Opt>0</Opt>
          <ItemCode>2001</ItemCode>
          <Name>Book</Name>
          <DocEntryAR2>100100</DocEntryAR2>
        </GoodService>
        <GoodService>
          <Opt>0</Opt>
          <ItemCode>2002</ItemCode>
          <Name>Desk</Name>
          <DocEntryAR2>100100</DocEntryAR2>
        </GoodService>
        <GoodService>
          <Opt>0</Opt>
          <ItemCode>2003</ItemCode>
          <Name>Key</Name>
          <DocEntryAR2>100100</DocEntryAR2>
        </GoodService>
      </ListOfGoodService>
    </TaxInvoice>
    <TaxInvoice>
      <DocEntryAR>200100</DocEntryAR>
      <BuyerDocNum>7722</BuyerDocNum>
      <BuyerName>Dani</BuyerName>
      <ListOfGoodService>
        <GoodService>
          <Opt>0</Opt>
          <ItemCode>5001</ItemCode>
          <Name>Lamp</Name>
          <DocEntryAR2>200100</DocEntryAR2>
        </GoodService>
        <GoodService>
          <Opt>0</Opt>
          <ItemCode>5002</ItemCode>
          <Name>Car</Name>
          <DocEntryAR2>200100</DocEntryAR2>
        </GoodService>
      </ListOfGoodService>
    </TaxInvoice>
    <TaxInvoice>
      <DocEntryAR>300100</DocEntryAR>
      <BuyerDocNum>2211</BuyerDocNum>
      <BuyerName>Nina</BuyerName>
      <ListOfGoodService>
        <GoodService>
          <Opt>0</Opt>
          <ItemCode>3001</ItemCode>
          <Name>Fan</Name>
          <DocEntryAR2>300100</DocEntryAR2>
        </GoodService>
      </ListOfGoodService>
    </TaxInvoice>
  </ListOfTaxInvoice>
</TaxInvoiceBulk>