Click here to Skip to main content
Sign Up to vote bad
good
I'm getting the following XML string using a HttpWebRequest in c#.
My response 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


4 solutions

// 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);
    }
}
  Permalink  
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];
        }
  Permalink  
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[^]
  Permalink  
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[^]
  Permalink  

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Christian Graus 544
1 OriginalGriff 258
2 Ron Beyer 256
3 samadhan_kshirsagar 229
4 Sergey Alexandrovich Kryukov 193
0 Sergey Alexandrovich Kryukov 7,007
1 Prasad_Kulkarni 3,815
2 OriginalGriff 3,557
3 _Amy 3,372
4 CPallini 2,975


Advertise | Privacy | Mobile
Web02 | 2.6.130617.1 | Last Updated 6 Jan 2013
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid