将xml数据表解析为数据集
本文关键字：数据集 xml 数据表 | 更新日期: 2023-09-27 18:27:25
我正在编写一个网页，该网页需要解析从API调用派生的XML中的数据，但XML被格式化为数据头、数据体类型模式。
我需要能够为ASP.net C#页面解析它，在那里我将把数据导入SQL Server表。。。任何帮助都将不胜感激！
有问题的XML是：
<?xml version="1.0" encoding="UTF-8"?>
<report>
<head>
<vars>
<var name="userId">selfservice</var>
<var name="networkId">607</var>
<var name="reportName">Advertiser Channel Report</var>
</vars>
</head>
<body>
<component name="">
<component-header>Data</component-header>
<component-body>
<table name="">
<table-header/>
<table-body>
<overview>
<overview_item property="Execution time:">
0.000 second(s)
</overview_item>
<overview_item entity="Days stat" property="Days stat:">10/21/2014 - 10/21/2014</overview_item>
<overview_item entity="Advertiser" property="Advertiser Name:"> Like _SS</overview_item>
</overview>
<data>
<data-header>
<dr>
<dv type="int">Sr. No.</dv>
<dv type="int">Advertiser ID</dv>
<dv type="string">Advertiser Name</dv>
<dv type="int">Channel ID</dv>
<dv type="string">Channel Name</dv>
<dv type="long">Channel Impressions</dv>
<dv type="timestamp">Daily</dv>
<dv type="long">Days stat Impressions</dv>
</dr>
</data-header>
<data-body>
<dr class="total">
<dv>46 row(s) total</dv>
<dv/>
<dv/>
<dv/>
<dv/>
<dv>1073</dv>
<dv/>
<dv>1073</dv>
</dr>
<dr>
<dv t="ns">1</dv>
<dv t="ns">596749</dv>
<dv t="s">DATest3_SS</dv>
<dv t="ns">22923</dv>
<dv t="s">CS2261</dv>
<dv t="ns">1</dv>
<dv t="ns">10/21/2014</dv>
<dv t="ns">1</dv>
</dr>
<dr>
<dv t="ns">2</dv>
<dv t="ns">596749</dv>
<dv t="s">DATest3_SS</dv>
<dv t="ns">22683</dv>
<dv t="s">CS2258</dv>
<dv t="ns">1</dv>
<dv t="ns">10/21/2014</dv>
<dv t="ns">1</dv>
</dr>
<dr>
<dv t="ns">3</dv>
<dv t="ns">596749</dv>
<dv t="s">DATest3_SS</dv>
<dv t="ns">12174</dv>
<dv t="s">CR5925</dv>
<dv t="ns">2</dv>
<dv t="ns">10/21/2014</dv>
<dv t="ns">2</dv>
</dr>
<dr>
<dv t="ns">4</dv>
<dv t="ns">596749</dv>
<dv t="s">DATest3_SS</dv>
<dv t="ns">26519</dv>
<dv t="s">CS2938</dv>
<dv t="ns">65</dv>
<dv t="ns">10/21/2014</dv>
<dv t="ns">65</dv>
</dr>
<dr>
<dv t="ns">5</dv>
<dv t="ns">596749</dv>
<dv t="s">DATest3_SS</dv>
<dv t="ns">22776</dv>
<dv t="s">CS2261</dv>
<dv t="ns">19</dv>
<dv t="ns">10/21/2014</dv>
<dv t="ns">19</dv>
</dr>
<dr>
<dv t="ns">6</dv>
<dv t="ns">596749</dv>
<dv t="s">DATest3_SS</dv>
<dv t="ns">2748</dv>
<dv t="s">CN4940</dv>
<dv t="ns">7</dv>
<dv t="ns">10/21/2014</dv>
<dv t="ns">7</dv>
</dr>
<dr>
<dv t="ns">7</dv>
<dv t="ns">596749</dv>
<dv t="s">DATest3_SS</dv>
<dv t="ns">4235</dv>
<dv t="s">CN3539</dv>
<dv t="ns">1</dv>
<dv t="ns">10/21/2014</dv>
<dv t="ns">1</dv>
</dr>
<dr>
<dv t="ns">8</dv>
<dv t="ns">596749</dv>
<dv t="s">DATest3_SS</dv>
<dv t="ns">25387</dv>
<dv t="s">CS264</dv>
<dv t="ns">3</dv>
<dv t="ns">10/21/2014</dv>
<dv t="ns">3</dv>
</dr>
<dr>
<dv t="ns">9</dv>
<dv t="ns">596749</dv>
<dv t="s">DATest3_SS</dv>
<dv t="ns">22779</dv>
<dv t="s">CS2261</dv>
<dv t="ns">3</dv>
<dv t="ns">10/21/2014</dv>
<dv t="ns">3</dv>
</dr>
<dr class="total">
<dv>46 row(s) total</dv>
<dv/>
<dv/>
<dv/>
<dv/>
<dv>1073</dv>
<dv/>
<dv>1073</dv>
</dr>
</data-body>
<data-footer/>
</data>
</table-body>
</table>
</component-body>
</component>
</body>
</report>
您可以使用Linq-to-XML或XPath来解析XML，然后使用实体框架（或直接的ADO.NET）将其插入数据库。
如果您的目标是在HTML页面中显示此XML，则可以使用XSLT将XML转换为HTML。这就是Windows Live Messenger用于会话日志和IIS用于跟踪日志的内容。
在使用转换指令创建XSLT之后，您只需要在XML中向XSLT添加一个引用，当您在浏览器中打开XML时，就会发生神奇的事情。
检查此样本：http://www.w3schools.com/xml/xml_xsl.asp
我可以用这个函数解决这个问题：
public DataTable parseReport(List<zapi.ClientReportResultItem> reportItems)
{
StringBuilder sb = new StringBuilder();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(reportItems[0].body);
StringReader sReader = new StringReader(xmlDoc.InnerXml);
System.Xml.Linq.XDocument d = System.Xml.Linq.XDocument.Load(sReader);
List<_headers> headers = new List<_headers>();
_headers header = new _headers();
var headerXml = from r in d.Descendants("data-header")
.Descendants("dr")
.Descendants("dv")
select new
{
dv = r.Value,
dv_type = r.Attribute("type").Value
};
foreach (var _val in headerXml)
{
header = new _headers();
header.name = _val.dv;
header.type = _val.dv_type;
headers.Add(header);
}
//Create Data Table
DataTable reportData = new DataTable();
foreach (var head in headers)
{
if (head.type == "timestamp")
reportData.Columns.Add(head.name, typeof(DateTime));
else if (head.type == "int")
reportData.Columns.Add(head.name, typeof(int));
else if (head.type == "string")
reportData.Columns.Add(head.name, typeof(string));
else if (head.type == "float")
reportData.Columns.Add(head.name, typeof(float));
else if (head.type == "long")
reportData.Columns.Add(head.name, typeof(long));
}
var data = from r in d.Descendants("data-body")
.Descendants("dr")
where r.HasAttributes == false
select new
{
dv = r.Elements()
};
foreach (var datavalue in data)
{
DataRow dr = reportData.NewRow();
for (int iCount = 0; iCount < datavalue.dv.Count(); iCount++)
{
dr[iCount] = datavalue.dv.ToArray()[iCount].Value;
}
reportData.Rows.Add(dr);
}
return reportData;
}