Parsing (and keeping) XML structure into SQL Server

Question

I'm looking to parse a relatively complex XML file through C# and store a selection of the data into a SQL Server '08 database. This is what I'm looking to extract from the XML file:

<educationSystem>
    <school>
        <name>Primary School</name>
        <students>
            <student id="123456789">
                <name>Steve Jobs</name>
                <other elements>More Data</other elements>
            </student>
            <student id="987654">
                <name>Jony Ive</name>
                <otherElements>More Data</otherElements>
            </student>
        </students>
    </school>
    <school>
        <name>High School</name>
        <students>
            <student id="123456">
                <name>Bill Gates</name>
                <other elements>More Data</other elements>
            </student>
            <student id="987654">
                <name>Steve Ballmer</name>
                <otherElements>More Data</otherElements>
            </student>
        </students>
    </school>
</educationSystem>

[Before you ask, no this isn't a school assignment - I'm using school/students as an example and because the original is a lot more sensitive.]

I'm able to (using XDocument/XElement) parse the XML file and get a list of all school names, student names and student ID's, but when this gets added to the database, I end up with the Bill Gates student entry being under a second school. It's all just line-by-line.

I'm looking to find a way to say, achieve this:

Foreach school
    put it's name into an XElement
    foreach student
        grab the name and id put into XElements
Grab next school and repeat

I believe Linq would be the best way to achieve this, but I'm having trouble in how to get started with the process. Would anyone be able to point me in the right direction?

Edit: Here's the code I'm currently using to save data to the database. It processes a list at a time (hence things aren't related as they should be). I'll also be tidying up the SQL as well.

 private void saveToDatabase (List<XElement> currentSet, String dataName)
    {
        SqlConnection connection = null;

        try
        {
            string connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString + "; Asynchronous Processing=true";
            connection = new SqlConnection(connectionString);
            connection.Open();

            foreach (XElement node in currentSet)
            {
                SqlCommand sqlCmd = new SqlCommand("INSERT INTO dbo.DatabaseName (" + dataName + ") VALUES ('" + node.Value + "')", connection);

                sqlCmd.ExecuteNonQuery();
            }
        }

Answer 1

Update: The original code example did not mention a namespace. Namespaces need to be either accounted for when searching for elements by XName or one needs to to search using the XName.LocalName property. Updated the example to show how to handle selecting elements in such a case.

namespace Stackover
{
    using System;
    using System.Xml.Linq;

    class Program
    {
        private const string Xml = @"<?xml version=""1.0"" encoding=""UTF-8""?>
<namespaceDocument xmlns=""http://www.namedspace/schemas"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:schemaLocation=""http://www.namedspace/schemas.xsd"">
<educationSystem>
    <school>
        <name>Primary School</name>
        <students>
            <student id=""123456789"">
                <name>Steve Jobs</name>
                <otherElements>
                    <dataA>data</dataA>
                </otherElements>
            </student>
            <student id=""987654"">
                <name>Jony Ive</name>
                <otherElements>
                    <dataB>data</dataB>
                </otherElements>
            </student>
        </students>
    </school>
    <school>
        <name>High School</name>
        <students>
            <student id=""123456"">
                <name>Bill Gates</name>
                <otherElements>
                    <dataC>data</dataC>
                </otherElements>
            </student>
            <student id=""987654"">
                <name>Steve Ballmer</name>
                <otherElements>
                    <dataD>data</dataD>
                </otherElements>
            </student>
        </students>
    </school>
</educationSystem>
</namespaceDocument>";


        static void Main(string[] args)
        {
            var root = XElement.Parse(Xml);
            XNamespace ns = "http://www.namedspace/schemas";
            foreach(var school in root.Descendants(ns + "school")) // or root.Descendants().Where(e => e.Name.LocalName.Equals("school"));
            {
                Console.WriteLine(school.Element(ns + "name").Value);

                foreach (var students in school.Elements(ns+ "students"))
                {

                    foreach (var student in students.Elements())
                    {
                        Console.WriteLine(student.Attribute("id"));
                        Console.WriteLine(student.Name); // Name = namespace + XName
                        Console.WriteLine(student.Name.LocalName); // no namespace
                    }
                }
            }
        }

    }

}

Answer 2

This LINQ will generate a Collection of Objects,with two properties

1. Name of the school

2. List of students(again a collection)

    var result = XElement.Load("data.xml")
                  .Descendants("school")
                  .Select( x => new { 
                            name = XElement.Parse(x.FirstNode.ToString()).Value,
                            students =x.Descendants("student")
                                       .Select(stud => new { 
                  id = stud.Attribute("id"),
                  name = XElement.Parse(stud.FirstNode.ToString()).Value})
              .ToList()});
   

   Note:The LINQ assumes <name> as the first node under <school> and <student> tags

3. Then you can use the foreach that you intended and it will work like a charm

    foreach (var school in result)
    {
       var schoolName = school.name;
       foreach (var student in school.students)
       {
               //Access student.id and student.name here                    
       }
    }
   

Answer 3

For this particular type of workings with XML data, you could use XML Serialization / Deserialization.

This will allow you to Deserialize your XML Data into a IEnumerable Class Object, Perform your LINQ Queries on this Class and then save to SQL.

Hope this helps.