linq C#从数据表创建XML文件

wpx232ag  于 2022-12-20  发布在  C#
关注(0)|答案(1)|浏览(133)

我是XML新手,希望从数据表创建XML文档
我有一个包含以下数据的DataTable:

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

我需要从上面的DataTable数据创建一个XML文件,如下所示:

<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>

我尝试了以下方法,但没有得到预期的结果:
我如何迭代数据表行来创建XML文档?

mnemlml8

mnemlml81#

基本问题是只创建了一个<Trust>元素,需要XML有多个重复的<Trust>元素,所以需要将这些元素的创建移到dt.AsEnumerable() lambda中。
<Id>元素也有一个bug:您正在创建名为<ID>而不是<Id>的元素。XML区分大小写,因此必须始终使用正确的大小写创建元素。
最后,一个建议:您将部门Number硬编码为"123A",但是您可以轻松地按Number列的值对DataTable行进行分组,然后写入组键而不是硬编码值。
将所有这些放在一起,您的代码应该如下所示:

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

应该不需要创建XDeclaration节点,当您将XElement序列化为最终输出流时,应该自动为您编写该节点。
演示小提琴here
但是我如何按“部门号”分组呢?我已经更新了数据表并期望XML。对不起,我没有在问题的前面提到这一点。
您可以添加一个额外的GroupBy(),以便按DeptNo对Department行进行分组,如下所示:

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

演示小提琴#2 here

相关问题