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

                        // Group Departments by DeptNo. Also collect DeptName and DeptCode in the Key to use later (we assume they are consistent for each DeptNo).

                        d.GroupBy(r => (DeptNo : r.Field<string>("DeptNo"), DeptName : r.Field<string>("DeptName"), DeptCode : r.Field<string>("DeptCode"))).Select(

                            deptNo => 

                            new XElement("Trust",

                                new XElement("DeptNo", deptNo.Key.DeptNo),  

                                new XElement("DeptName", deptNo.Key.DeptName),   

                                new XElement("DeptCode", deptNo.Key.DeptCode),

                                deptNo.Select(data =>

                                    new XElement("DepartmentData", 

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

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

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

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

                                    )

                                )

                            )

                        )

                    )

                )

            );

​

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

            // Strip the comment

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

​

            Console.WriteLine(root);

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

        }

        public static DataTable GetDataTable()

        {

​

            var csvText = 

#if false

"""

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

123A,1004,THEO JR,AZ,07/12/2021,2021,74-126,N

123A,1005,HARR JR,NV,06/12/2021,2021,74-127,Y

123A,1001,DESC JR,FED,06/12/2021,2021,74-123,N

123A,1002,PHIL JR,FED,09/12/2021,2021,74-124,N

""";

#else

// Fixed to make consistent with XML

"""

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

123A,1001,DESC JR,FED,06/12/2021,2021,74-123,N

123A,1002,PHIL JR,FED,09/12/2021,2021,74-124,N

""";

#endif

            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>

               <DepartmentData>

                     <State>FED</State>

                     <Date>06/12/2021</Date> <!-- FIXED -->

                     <Year>2021</Year>

                     <ELP>N</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>

               <DepartmentData>

                     <State>FED</State>

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

                     <Year>2021</Year>

                     <ELP>N</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;

            }

        }

    }

​

    public static class StringHelper

    {

        public static int IndexOfFirstDifference(this string a, string b)

        {

            int index = 0;

            int max = Math.Min(a.Length, b.Length);

            while (index < max && a[index] == b[index]) 

                index++;

            if (index >= a.Length || index >= b.Length)

                index = -1;

            return index;

        }

    }

​