Export the following XML to Datatable

Question

3.00/5 (2 votes)

See more:

I'm getting the following XML string using a HttpWebRequest in c#.
My response XML

XML

<response>
    <accounts>
        <account>
            <city>Some City</city>
            <accountId>1234567</accountId>
            <companyName>My Company</companyName>
            <country>My Country</country>
            <email>myemail@yahoo.com</email>
            <enabled>1</enabled>
        </account>
        <account>
            <city>Some Other City</city>
            <accountId>0987654</accountId>
            <companyName>My Other Company</companyName>
            <country>My Country</country>
            <email>myotheremail@yahoo.com</email>
            <enabled>1</enabled>
        </account>
    </accounts>
    <total>2</total>
    <limit>20</limit>
    <offset>0</offset>
</response>

I need to export the XML to a datatable with the following schema.

accountId | companyName      | city            | country
1234567   | My Company       | Some City       | My Country
0987654   | My Other Company | Some Other City | My Country

This is my first work with XML and I'm confused how to do it. Please help me.

Posted 5-Jan-13 15:54pm

KrishanuDey

Add a Solution

4 solutions

Solution 4

Hi,
Use this piece of code:

DataSet ds = new DataSet();
       DataTable dt = null;
       ds.ReadXml("Xml File Path");
       if(ds.Tables[0]!=null)
       {
           dt=new DataTable();
           dt = ds.Tables[0];
       }

Posted 6-Jan-13 7:53am

Aditya Magotra

Updated 6-Jan-13 7:54am

v2

Solution 1

Create a Datatable with the columns that you require, then populate it via Linq-to-XML. You can use a Select query to create an object that represents each row, then use the standard approach for creating datarows for each item.

The below has plenty of details to get you started..
http://support.microsoft.com/kb/311566[^]

Posted 5-Jan-13 17:23pm

Pradeep Shukla

Solution 2

Create a stored procedure which accepts an XML as a parameter. Then retrieve the values from XML parameter and insert to the database table.

following is a good article explaining this. (You no need to consider about c# code that serializes an object into XML as you already have the XML)

http://www.smallworkarounds.net/2009/07/sending-list-of-complex-objects-to-sql.html[^]

Posted 5-Jan-13 17:49pm

Tharaka MTR

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

KrishanuDey · Accepted Answer · 2013-01-06T03:16:00

C#

// Forming a DataTable
dt = new DataTable("Accountdata"); // System.Data.DataTable
dt.Columns.Add("Account ID");
dt.Columns.Add("Company Name");
dt.Columns.Add("City");
dt.Columns.Add("Country");
dt.Columns.Add("Email");
dt.Columns.Add("Enabled");

XmlDocument XMLDoc = new XmlDocument(); // System.Xml.XmlDocument
Stream responseStream = response.GetResponseStream(); // "response" is the HttpWebResponse object that holds the response xml of the request
XMLDoc.Load(responseStream);
foreach (XmlNode AccData in XMLDoc.SelectNodes("/response/accounts/account"))
{
    if (AccData["accountId"] == null)
    {
        continue;
    }
    else
    {
        // Fill the DataTable line by line
        int AccountId = Convert.ToInt32(AccData["accountId"].InnerText);
        string CompanyName = AccData["companyName"].InnerText;
        string City = AccData["city"].InnerText;
        string Country = AccData["country"].InnerText;
        string Email = AccData["email"].InnerText;
        int Enabled = Convert.ToInt32(AccData["enabled"].InnerText);
        dt.Rows.Add(AccountId, CompanyName, City, Country, Email, Enabled);
    }
}