Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 16:54pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

// 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  
Rate this: bad
good
Please Sign up or sign in to vote.

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];
        }
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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[^]
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,192
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 6 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100