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();
            }
        }

I take it your document has more than one "school"? If so you should have included an example with multiple schools (and an appropriate container for school). The example as posted here has only one school so we can't speculate on why the students are getting messed up. Also showing us the code you use to place your elements in the database would be helpful. — Anthill
– Anthill, Commented Nov 9, 2012 at 10:22
Thanks for your comment. I've included the code, and yes you're right - there is multiple schools which I'm now also showing above. :) — edparry
– edparry, Commented Nov 9, 2012 at 10:51
Talking about a melinda gates data item when it isn't in your sample data can lead to confusion. In me at least... :) — Chris
– Chris, Commented Nov 9, 2012 at 15:37
Apologies, @Chris, that was left behind before an edit. Updated now. — edparry
– edparry, Commented Nov 9, 2012 at 16:08

Prabhu Murthy · Accepted Answer · 2012-11-09 15:36:15Z

1

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

Name of the school

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

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                    
    }
 }

edited Nov 9, 2012 at 15:36

answered Nov 9, 2012 at 15:02

Prabhu Murthy

9,2865 gold badges32 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

edparry Over a year ago

Thanks for this. I'm having a weird problem with it though. I've followed the code from above, and it compiles and is happy - after the foreach loops, I can call school.name and it's aware that there is a name within. But the code isn't running through the foreach. When debugging, it gets to the foreach but then passes over it. Any ideas why this could be?

edparry Over a year ago

When I do a count on "result", I get 0 back, but no compile errors. Could this have something to do with the fact that "school" isn't the first element in the XML file?

Prabhu Murthy Over a year ago

are u using the same xml you have posted in your question.?

edparry Over a year ago

no, there's a little more above it. This is the top of my XML, down to the first "School" tag: <?xml version="1.0" encoding="UTF-8"?>  <SchoolDocument xmlns="www.***.co.uk/schemas" xmlns:xsi="w3.org/2001/XMLSchema-instance" xsi:schemaLocation="www.***.co.uk/schemas ***_Extract_Schema.xsd"> <Data> <SchoolCollection> <Version>1.0</Version> <ProcessingDate>2012-02-20</ProcessingDate> <Created>2012-02-17</Created> <School SchoolCode="0115">

edparry Over a year ago

Yeah, everything appears to be in the right case, so .Descendants should be able to find it, but it's still returning 0.

|

Derek · Accepted Answer · 2012-11-09 10:26:41Z

0

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.

answered Nov 9, 2012 at 10:26

Derek

8,70813 gold badges61 silver badges95 bronze badges

Comments

Anthill · Accepted Answer · 2012-11-15 23:43:23Z

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
                    }
                }
            }
        }

    }

}

Collectives™ on Stack Overflow

Parsing (and keeping) XML structure into SQL Server

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related