locked
Problem in getting XML Element Values!!! RRS feed

  • Question

  • Hi,

    Let me show the xml here first which I am working on:

    <BookStore> 
        <Language>en-gb</Language> 
        <Profile> 
            <Sender>61</Sender> 
            <Receiver>81</Receiver> 
        </Profile> 
        <Record> 
            <Status>New</Status> 
            <Info> 
                <Category>Fiction</Category> 
                <Number>518</Number> 
                <Account>113</Account> 
                <Amount>36</Amount> 
                <Date>2008-06-02</Date> 
            </Info> 
        </Record> 
        <Record> 
            <Status>New</Status> 
            <Info> 
                <Category>Comic</Category> 
                <Number>526</Number> 
                <Account>126</Account> 
                <Amount>131</Amount> 
                <Date>2008-06-02</Date> 
            </Info> 
        </Record> 
    </BookStore>>  
     
     


    My target is to get value from each element and save it inside a MSSQL DB table. If you see the Record tag has appeared in the xml twice (and could be more). Till now if the xml file contains the tag only once, I am able to successfully grab the value by using the following code:

    XmlDocument xDoc = new XmlDocument();  
                xDoc.Load(fileName);  
     
                 XmlNodeList Account = xDoc.GetElementsByTagName("Account");  
                 string account = Account.Item(0).InnerText;  
     
                 XmlNodeList Amount= xDoc.GetElementsByTagName("Amount");  
                 string amount = Amount.Item(0).InnerText;  
     
    .............so on....
     

    But when the same tag appears multiple times, I am getting difficulty in get the values 'N' of times and putting it inside 'variableN' in order to insert in the database.

    Can someone please guide me how to achieve this?

    Regards,

    Khurram.
    • Moved by Peter Ritchie Monday, June 2, 2008 7:05 PM more appropriate forum
    Monday, June 2, 2008 6:20 PM

Answers

  • First of all your XML is in error, you have </BookStore>> on the last line.

    You could do something like this:

    using System;

    using System.Collections.Generic;

    using System.Text;

    using System.Xml;

    namespace ConsoleApplication1

    {

    public class Info

    {

    private string category, number, account, amount, date;

    public Info(string category, string number,

    string account, string amount, string date)

    {

    this.category = category;

    this.account = account;

    this.number = number;

    this.date = date;

    this.amount = amount;

    }

    public string Category

    {

    get { return category; }

    }

    public string Amount

    {

    get { return amount; }

    }

    public string Number

    {

    get { return number; }

    }

    public string Date

    {

    get { return date; }

    }

    public string Account

    {

    get { return account; }

    }

    }

    class Program

    {

    static void Main(string[] args)

    {

    List<Info> myList = new List<Info>();

    XmlDocument xDoc = new XmlDocument();

    xDoc.Load(Environment.CurrentDirectory + @"\d.xml");

    XmlNodeList Record = xDoc.SelectNodes("/BookStore/Record");

    foreach (XmlNode node in Record)

    {

    foreach (XmlNode myNode in node.SelectNodes("//Info"))

    {

    myList.Add(new Info(myNode["Category"].InnerText, myNode["Number"].InnerText, myNode["Account"].InnerText, myNode["Amount"].InnerText, myNode["Date"].InnerText));

    }

     

    foreach (Info i in myList)

    {

         Console.WriteLine("Amount = " + i.Account);

    }

    }

    }

    }




    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Monday, June 2, 2008 7:18 PM One last update
    • Proposed as answer by khurram007 Monday, June 2, 2008 8:43 PM
    • Marked as answer by khurram007 Tuesday, June 3, 2008 1:04 PM
    Monday, June 2, 2008 7:10 PM

All replies

  •  For questions regarding XML in the .NET Framework, see the XML in the .NET Framework forum.
    http://www.peterRitchie.com/blog
    • Marked as answer by Peter Ritchie Monday, June 2, 2008 7:05 PM
    • Unmarked as answer by khurram007 Monday, June 2, 2008 7:09 PM
    Monday, June 2, 2008 7:04 PM
  • Thanks Peter!

    I am right here waiting for the help.

    Regards,

    Khurram.
    Monday, June 2, 2008 7:09 PM
  • First of all your XML is in error, you have </BookStore>> on the last line.

    You could do something like this:

    using System;

    using System.Collections.Generic;

    using System.Text;

    using System.Xml;

    namespace ConsoleApplication1

    {

    public class Info

    {

    private string category, number, account, amount, date;

    public Info(string category, string number,

    string account, string amount, string date)

    {

    this.category = category;

    this.account = account;

    this.number = number;

    this.date = date;

    this.amount = amount;

    }

    public string Category

    {

    get { return category; }

    }

    public string Amount

    {

    get { return amount; }

    }

    public string Number

    {

    get { return number; }

    }

    public string Date

    {

    get { return date; }

    }

    public string Account

    {

    get { return account; }

    }

    }

    class Program

    {

    static void Main(string[] args)

    {

    List<Info> myList = new List<Info>();

    XmlDocument xDoc = new XmlDocument();

    xDoc.Load(Environment.CurrentDirectory + @"\d.xml");

    XmlNodeList Record = xDoc.SelectNodes("/BookStore/Record");

    foreach (XmlNode node in Record)

    {

    foreach (XmlNode myNode in node.SelectNodes("//Info"))

    {

    myList.Add(new Info(myNode["Category"].InnerText, myNode["Number"].InnerText, myNode["Account"].InnerText, myNode["Amount"].InnerText, myNode["Date"].InnerText));

    }

     

    foreach (Info i in myList)

    {

         Console.WriteLine("Amount = " + i.Account);

    }

    }

    }

    }




    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Monday, June 2, 2008 7:18 PM One last update
    • Proposed as answer by khurram007 Monday, June 2, 2008 8:43 PM
    • Marked as answer by khurram007 Tuesday, June 3, 2008 1:04 PM
    Monday, June 2, 2008 7:10 PM
  • Thanks John,

    But I am using ASP.Net not Windows. I am a beginner but the code seems to be of Windows Platform.

    BTW, I tried to change a few things, rectifying all errors but the Cosole.Writeline is printing each value two times. Any idea?

    Regards,

    Khurram.
    Monday, June 2, 2008 8:21 PM
  • I am sorry, I was trying to help you too fast. Get rid of the code I commented..

    //XmlNodeList Record = xDoc.SelectNodes("/BookStore/Record");

    //foreach (XmlNode node in Record)

    //{

    foreach (XmlNode myNode in xDoc.SelectNodes("//Info"))

    {

    myList.Add(new Info(myNode["Category"].InnerText, myNode["Number"].InnerText, myNode["Account"].InnerText, myNode["Amount"].InnerText, myNode["Date"].InnerText));

    }

    //}


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, June 2, 2008 8:25 PM
  • Great John... Its working now :)

    Now the later part of the question --- Adding in MSSQL Database...

    Is there a way I can store those values in a dynamic variables like amount1,amount2, etc. I actually want to finish working with XML and only after this start working with the database.

    Is it possible?

    Thanks again for the excellent help so far provided by you.

    Regards,

    Khurram.
    Monday, June 2, 2008 8:33 PM
  • Just perform an INSERT statement during each info class iteration


    //I'll assume you already have a connection object, so we will call it "cn"

    string sql = "INSERT into myTableName (Category, Number, Account, Amount, Date) Values (@Cat, @Num, @Acc, @Amt, @Dt)";

    foreach (Info i in myList)
    {
             cn.Open();
             using (SqlCommand cmd = new SqlCommand(sql, cn))
             {
                    //Category
                    SqlParameter param = new SqlParameter("@Cat", SqlDbType.VarChar, 20);
                    param.Value = i.Category;
                    cmd.Parameters.Add(param);
                                 
                    //Number
                    SqlParameter param = new SqlParameter("@Num", SqlDbType.VarChar, 20);
                    param.Value = i.Number;
                    cmd.Parameters.Add(param);

                    //Account
                    SqlParameter param = new SqlParameter("@Acc", SqlDbType.VarChar, 20);
                    param.Value = i.Account;
                    cmd.Parameters.Add(param);

                    //Amount
                    SqlParameter param = new SqlParameter("@Amt", SqlDbType.VarChar, 20);
                    param.Value = i.Amount;
                    cmd.Parameters.Add(param);

                    //Date
                    SqlParameter param = new SqlParameter("@Dt", SqlDbType.VarChar, 20);
                    param.Value = i.Date;
                    cmd.Parameters.Add(param);

                    //Execute Insert
                    cmd.ExecuteNonQuery():
                    cn.Close();                      
               }             
                   
    }



    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Monday, June 2, 2008 9:05 PM Update
    • Proposed as answer by khurram007 Tuesday, June 3, 2008 1:03 PM
    Monday, June 2, 2008 8:50 PM
  • Thanks John!

    Its all working now. This is my first experience in MSDN forums and I must say you made it memorable. :)

    Thanks AGAIN!

    Regards,

    Khurram.
    Tuesday, June 3, 2008 1:03 PM
  • Your quite welcome! And thank you for the compliment..
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Tuesday, June 3, 2008 4:22 PM
  • Hi John,

    That is a compliment you earned actually! :)

    In the same foreach loop case in xml, I have another scenario where I have to go one more level deep. If you could help me out, should I post another thread for it and post the URL here?

    Waiting for your response.

    Regards,

    Khurram.


    Wednesday, June 4, 2008 6:54 AM
  • Hi John,

    I have started another thread for the query I told you in above post. The URL is

    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3441548&SiteID=1

    Please help me with this one as well. I know you are able to do it :)

    Thanks again!

    Regards,

    Khurram.
    Wednesday, June 4, 2008 12:22 PM