C# create XML file from Datatable-CodePudding

I am new to XML and want to create XML document from datatable

I have a DataTable with following data:

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

I need to create an XML file from above DataTable data as below:

<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>12/12/2021</Date>
                     <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>

I tried the following but it is not giving me the expected result:

How can I iterate through datatable rows to create XML document?

CodePudding user response：

Your basic problem is that you are only creating one <Trust> element. You required XML has multiple repeating <Trust> elements, so you need to move creation of these elements inside the dt.AsEnumerable() lamda.

You also have a bug with the <Id> element: you are creating an element named <ID> not <Id>. XML is case sensitive, so elements must always be created with the correct casing.

Finally, a suggestion: you are hardcoding the department Number to be "123A", however you can easily group your DataTable rows by the value of the Number column, then write the group key instead of the hardcoded value.

Putting it all together, your code should look like:

// 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"))
                    )
                )
            )
        )
    )
);

There should be no need to create the XDeclaration node, it should be written for you automatically when you serialize your XElement to your final output stream.

Demo fiddle here.

But how do I group by 'DeptNo'? I have updated the DataTable and expected XML. Sorry I didn't mention this earliar in the question.

You can add an extra GroupBy() to additionally group the Department rows by DeptNo, like so:

// 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"))
                        )
                    )
                )
            )
        )
    )
);

Demo fiddle #2 here.