Joe Kuzma
Reputation: 11
Parse xml data-table to dataset
I'm programming a web page that will need to parse data from XML derived from an API call, but the XML is formatted into a data-header, data-body type schema.
I need to be able to parse this for an ASP.net C# page, where I will be importing the data into a SQL Server table... Any help would be appreciated!
The XML in question is:
<?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>
Upvotes: 1
Views: 1020
Answers (3)
Joe Kuzma
Reputation: 11
I was able to resolve this with this function:
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;
}
Upvotes: 0
asumaray
Reputation: 21
You can use Linq-to-XML or XPath to parse the xml and then use Entity Framework (or straight ADO.NET) to insert it into the database.
Upvotes: 2
nflash
Reputation: 430
If your goal is to display this XML in an HTML page you could use XSLT to transform the XML in HTML. This was what Windows Live Messenger used for conversation logs and what IIS uses for tracing logs.
After creating the XSLT with the transform instructions you only need to add an reference in the XML to the XSLT and magic happens when you open the XML in a browser.
check this sample: http://www.w3schools.com/xml/xml_xsl.asp
Upvotes: 0
Related Questions
- problem when parsing from xml to a dataTable
- Parse XML to a datatable
- Convert Xml to DataTable
- DataSet.DataTable.DataRow (Single) to XML String
- How to convert unfamiliar xml into datatable
- How i can read xml into DataTable type object?
- Parsing Datatable to XML Document
- Reading data from xml to datatable
- C# XMLDocument to DataTable?
- Converting XML to a DataTable