How to fetch values from XML and iterate to store in variable

Question

I have the XML in the below form.

<TxLife>
  <Policy>
    <PolNumber>BB1062632</PolNumber>
    <FirstName>EWAN</FirstName>
    <LastName>MCKENZIE</LastName>
    <BirthDate>1972-03-08</BirthDate>
    <MailType>ABC</MailType>
  </Policy>
  <Policy>
    <PolNumber>BB1234567</PolNumber>
    <FirstName>TEST</FirstName>
    <LastName>ONE</LastName>
    <BirthDate>1972-03-08</BirthDate>
    <MailType>DEF</MailType>
 </Policy>
</TxLife>

I have written the code to fetch the value in string array with the below code:

 string[] arr = doc.Descendants("PolNumber").Select(element => element.Value).ToArray();

But not sure how to get individual values and store in variable

Answer 1

Option 1 : Deserialization

Instead of using Linq To XML, you could deserialize your object and store the values.

var serializer = new XmlSerializer(typeof(TxLife));
using (TextReader reader = new StringReader(strXml))
{
    var result = (TxLife)serializer.Deserialize(reader);
}

Where TxLife is defined as

[XmlRoot(ElementName="Policy")]
public class Policy 
{
    [XmlElement(ElementName="PolNumber")]
    public string PolNumber { get; set; }
    [XmlElement(ElementName="FirstName")]
    public string FirstName { get; set; }
    [XmlElement(ElementName="LastName")]
    public string LastName { get; set; }
    [XmlElement(ElementName="BirthDate")]
    public string BirthDate { get; set; }
    [XmlElement(ElementName="MailType")]
    public string MailType { get; set; }
}

[XmlRoot(ElementName="TxLife")]
public class TxLife {
    [XmlElement(ElementName="Policy")]
    public List<Policy> Policy { get; set; }
}

Option 2 : Linq To Xml

In case you want to stick to Linq To Xml, You can use

var root = XElement.Parse(strXml);
var policies = new List<Policy>();
foreach(var item in root.Descendants("Policy"))
{
    policies.Add(new Policy
    {
     PolNumber= item.Element("PolNumber").Value,
     FirstName = item.Element("FirstName").Value,
     LastName = item.Element("LastName").Value,
     BirthDate = item.Element("BirthDate").Value,
      MailType = item.Element("MailType").Value
    });
}

Answer 2

Rather than use Anu solution with for loop, use just linq with select which is more efficient. Also value can give issue when element does not exist so cast to string is better. See code below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            string connStr = "Enter your connection string here";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "WB_SetNotification";  //stored procdure name

            SqlParameter policyNumber = cmd.Parameters.Add(new SqlParameter("@PolicyNumber", SqlDbType.VarChar, 20));
            cmd.Parameters.Add(policyNumber);
            cmd.Parameters["@PolicyNumber"].Direction = ParameterDirection.Input;

            SqlParameter firstName = cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(firstName);
            cmd.Parameters["@FirstName"].Direction = ParameterDirection.Input;

             SqlParameter lastName = cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(lastName);
            cmd.Parameters["@LastName"].Direction = ParameterDirection.Input;

            SqlParameter dob = cmd.Parameters.Add(new SqlParameter("@DateOfBirth", SqlDbType.DateTime));
            cmd.Parameters.Add(dob);
            cmd.Parameters["@DateOfBirth"].Direction = ParameterDirection.Input;

            XDocument doc = XDocument.Load(FILENAME);

            List<Policy> policies = doc.Descendants("Policy").Select(item => new Policy()
            {
                PolNumber = (string)item.Element("PolNumber"),
                FirstName = (string)item.Element("FirstName"),
                LastName = (string)item.Element("LastName"),
                BirthDate = (string)item.Element("BirthDate"),
                MailType = (string)item.Element("MailType")
            }).ToList();

            foreach (Policy policy in policies)
            {
                cmd.Parameters["@PolicyNumber"].Value  = policy.PolNumber;
                cmd.Parameters["@FirstName"].Value = policy.FirstName;
                cmd.Parameters["@LastName"].Value = policy.LastName;
                cmd.Parameters["@DateOfBirth"].Value = policy.BirthDate;

                cmd.ExecuteNonQuery();
            }


        }
        public class Policy
        {
            public string PolNumber { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string BirthDate { get; set; }
            public string MailType { get; set; }
        }
    }
}