在c#中将数据表转换为XML层次结构
本文关键字:XML 层次结构 转换 数据表 | 更新日期: 2023-09-27 18:11:46
我有以下数据表:
A B C
----------
A1 B1 C1
A1 B1 C2
A1 B1 C3
A1 B2 C1
A1 B2 C2
----------
我试着用c#把它转换成XML格式,像这样:
<Data>
<A>
<lable>A1</lable>
<B>
<lable>B1</lable>
<C>
<lable>C1</lable>
<lable>C2</lable>
<lable>C3</lable>
</C>
<lable>B2</lable>
<C>
<lable>C1</lable>
<lable>C2</lable>
</C>
</B>
</A>
</Data>
我做了一个深入的搜索,我在网上找到了一些有用的东西。但它使用了关系,而我的数据只有一个表。我还尝试了这个c#代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Linq;
using System.Data.SqlClient;
SqlConnection con = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Files;Data Source=localhost");
con.Open();
SqlCommand cmd = new SqlCommand("select * from MyTable",con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable DT = new DataTable("Data");
da.Fill(DT);
dataGridView1.DataSource = DT;
con.Close();
string XML = ToXmlFormat(DT, 0);
Console.WriteLine(XML);
public string ToXmlFormat(this DataTable table, int metaIndex = 0)
{
XDocument xdoc = new XDocument(
new XElement(table.TableName,
from column in table.Columns.Cast<DataColumn>()
where column != table.Columns[metaIndex]
select new XElement(column.ColumnName,
from row in table.AsEnumerable()
select new XElement(row.Field<string>(metaIndex), row[column])
)
)
);
return xdoc.ToString();
}
结果是另一种格式,如我所解释的,它不是嵌套XML。见图片
我如何将我的表转换成我需要的XML格式?
您需要使用GroupBy
对行进行分组,然后选择您想要的部分进入XElements
。
下面的代码应该做你想做的:
var xml = new XElement(table.TableName, table.Rows.Cast<DataRow>()
.GroupBy(row => (string)row[0])
.Select(g =>
new XElement(table.Columns[0].ColumnName,
new XElement("label", g.Key),
g.GroupBy(row => (string)row[1])
.Select(g1 =>
new XElement(table.Columns[1].ColumnName,
new XElement("label", g1.Key),
new XElement(table.Columns[2].ColumnName,
g1.Select(row =>
new XElement("label", (string)row[2])
)
)
)
)
)
)
).ToString();
小提琴:https://dotnetfiddle.net/qEWNvj
我的风格和别人的回答有点不同。代码经过测试,符合您的预期结果。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Xml.Linq;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable();
dt.Columns.Add("A", typeof(string));
dt.Columns.Add("B", typeof(string));
dt.Columns.Add("C", typeof(string));
dt.Rows.Add(new object[] {"A1", "B1","C1"});
dt.Rows.Add(new object[] {"A1", "B1","C2"});
dt.Rows.Add(new object[] {"A1", "B1","C3"});
dt.Rows.Add(new object[] {"A1", "B2","C1"});
dt.Rows.Add(new object[] {"A1", "B2","C2"});
dt = dt.AsEnumerable()
.OrderBy(x => x.Field<string>("A"))
.ThenBy(x => x.Field<string>("B"))
.ThenBy(x => x.Field<string>("C"))
.CopyToDataTable();
XElement data = new XElement("Data", new XElement("A", dt.AsEnumerable()
.GroupBy(g1 => g1.Field<string>("A")).Select(g1a => new object[] {
new XElement("lable",(string)g1a.Key),
new XElement("B",
g1a.GroupBy(g2 => g2.Field<string>("B")).Select(g2b => new object[] {
new XElement("lable", (string)g2b.Key),
new XElement("C",
g2b.Select(g3c => new XElement("lable", g3c.Field<string>("C"))
))}
))}
)));
}
}
}