using System;

using System.Collections.Generic;

using System.Text;

using System.Linq;

using System.Xml.Serialization;

using System.Xml;

using System.Xml.Linq;

using System.IO;

using System.Data;

using System.Globalization;

​

using NUnit.Framework; //NUnit.Framework.AssertionException

​

//https://stackoverflow.com/questions/73127531/how-to-add-a-new-node-to-an-existing-xml-using-c-sharp

​

    public class TestClass

    {

        public static void Test()

        {

            var dt = GetDataTable();

​

            // Group the rows by Department (all 123A in your question).

            var departments = dt.AsEnumerable().GroupBy(r => r.Field<string>("Number")); 

​

            // Generate the root element

            var root = new XElement("Root",

                new XElement("Type", "MyType"),

                new XElement("FileDate", "12/15/2022"),

                new XElement("Version", "2.0"),

                departments.Select(

                    d =>

                    new XElement("Department",

                        new XElement("Number", d.Key),

                        new XElement("Id", "0000"),  // Fixed, was ID

                        d.Select(

                            r => 

                            new XElement("Trust",

                                new XElement("DeptNo", r.Field<string>("DeptNo")),  

                                new XElement("DeptName", r.Field<string>("DeptName")),   

                                new XElement("DeptCode", r.Field<string>("DeptCode")),

                                new XElement("DepartmentData", 

                                    new XElement("State", r.Field<string>("State")),

                                    new XElement("Date", r.Field<string>("Date")),

                                    new XElement("Year", r.Field<string>("Year")),

                                    new XElement("ELP", r.Field<string>("ELP"))

                                )

                            )

                        )

                    )

                )

            );

            Console.WriteLine(root);

​

            var required = XElement.Parse(GetRequiredXml());

            // Strip the comment

            required.DescendantNodes().Where(x => x.NodeType == XmlNodeType.Comment).Remove();

​

            Assert.That(XNode.DeepEquals(root, required));

        }

        public static DataTable GetDataTable()

        {

            var csvText = 

"""

Number,DeptNo,DeptName,State,Date,Year,DeptCode,ELP

123A,1001,DESC JR,PA,12/12/2021,2021,74-123,Y

123A,1002,PHIL JR,NY,09/12/2021,2021,74-124,Y

123A,1003,GILB JR,CA,08/12/2021,2021,74-125,N

""";

            using (var reader = new StringReader(csvText))

            using (var csv = new CsvHelper.CsvReader(reader, CultureInfo.InvariantCulture))

            {

                // Do any configuration to `CsvReader` before creating CsvDataReader.

                using (var dr = new CsvHelper.CsvDataReader(csv))

                {       

                    var dt = new DataTable();

                    dt.Load(dr);

                    return dt;

                }

            }               

        }

        static string GetRequiredXml() => 

"""

<Root>

    <Type>MyType</Type>

    <FileDate>12/15/2022</FileDate>

    <Version>2.0</Version>

    <Department>

          <Number>123A</Number>

          <Id>0000</Id>

          <Trust>

               <DeptNo>1001</DeptNo>

               <DeptName>DESC JR</DeptName>

               <DeptCode>74-123</DeptCode>

               <DepartmentData>

                     <State>PA</State>

                     <Date>12/12/2021</Date>

                     <Year>2021</Year>

                     <ELP>Y</ELP>

               </DepartmentData>

          </Trust>              

          <Trust>

               <DeptNo>1002</DeptNo>

               <DeptName>PHIL JR</DeptName>

               <DeptCode>74-124</DeptCode>

               <DepartmentData>

                     <State>NY</State>

                     <Date>09/12/2021</Date>

                     <Year>2021</Year>

                     <ELP>Y</ELP>

               </DepartmentData>

          </Trust>

          <Trust>

               <DeptNo>1003</DeptNo>

               <DeptName>GILB JR</DeptName>

               <DeptCode>74-125</DeptCode>

               <DepartmentData>

                     <State>CA</State>

                     <Date>08/12/2021</Date>

                     <Year>2021</Year>

                     <ELP>N</ELP>

               </DepartmentData>

          </Trust>

          <!-- Other Trust nodes skipped -->

    </Department>

</Root>

""";    

    }

​

    public class Program

    {

        public static void Main()

        {

            Console.WriteLine("Environment version: {0} ({1}, {2})", System.Runtime.InteropServices.RuntimeInformation.FrameworkDescription , Environment.Version, Environment.OSVersion);

            Console.WriteLine();

​

            try

            {

                TestClass.Test();

            }

            catch (Exception ex)

            {

                Console.WriteLine("Failed with unhandled exception: ");

                Console.WriteLine(ex);

                throw;

            }

        }

    }

​