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?
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() lambda.
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.