asked on

How do I parse the data from XML in C# into a DataTable

I have some XML that I am having trouble parsing the data into a DataTable, or some format to import into a SQL database...

The XML is like:

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

Open in new window

Thanks

ste5an

What SQL database? SQL Server? Is it a single import or a reoccuring task?

adworldmedia

ASKER

Its a process that is run daily, that will import that data into a SQL Server 2008 database table...

Once I can parse the data, I can figure out how to import into the database... just extracting the data has perplexed me...

Fernando Soto

From the XML that you will be importing the data from which, given in your question, what XML elements will constitute a row in the data table that will then be Imported into SQL server?

adworldmedia

ASKER

The <data-header> for the column names; and the <data-body> for the contents. Ignore the totals...

SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial