XML to SQL with C#

Question

I am developing a C# application to import about 26,000 XML records to a SQL Server database to be reformatted and imported into a new application. I already have a database designed with two tables. I have a students table with Student_ID as the primary key and a table called Student_Grades that includes all of the grades and also includes Student_ID as it's primary key and foreign key. All of the columns that are relevant are in the tables and are ready to receive data. I also have a view called sview that combines all of the information together. I am not trying to generate columns with the XML files, just transfer the data to the existing database. Now all that is left is taking the XML files and creating the application. I have a basic idea on how to do this, but any advice will be very much appreciated. One thing I have not figured out yet is how to do this with multiple XML files. I have around 20 xml files I need to be able to individually upload and insert into the database.

<Student>
            <STUDENT_ID>a0068d</STUDENT_ID>
            <ENTRY_VERSION>6</ENTRY_VERSION>
            <TYPE>12</TYPE>
            <NAME>John Doe</NAME>
            <LANGUAGE>EN</LANGUAGE>
            <COMMENTS>Excellent Behavior</COMMENTS>
            <USERNAME>admin</USERNAME>
            <STUDENT_GRADES>
                <STUDENT_GRADE>
                    <NAME>Biology</NAME>
                    <VALUE>A</VALUE>
                    <INHERITED>false</INHERITED>
                </STUDENT_GRADE>
                <STUDENT_GRADE>
                    <NAME>English</NAME>
                    <VALUE>C</VALUE>
                    <INHERITED>false</INHERITED>
                </STUDENT_GRADE>
                <STUDENT_GRADE>
                    <NAME>Math</NAME>
                    <VALUE>B</VALUE>
                    <INHERITED>false</INHERITED>
                </STUDENT_GRADE>
                <STUDENT_GRADE>
                    <NAME>Greek</NAME>
                    <VALUE></VALUE>
                    <INHERITED>true</INHERITED>
                </STUDENT_GRADE>
            </STUDENT_GRADES>
</Student>
<Student>
            <STUDENT_ID>b0362f</STUDENT_ID>
            <ENTRY_VERSION>3</ENTRY_VERSION>
            <TYPE>5</TYPE>
            <NAME>Jane Doe</NAME>
            <LANGUAGE>EN</LANGUAGE>
            <COMMENTS>Takes Insulin Daily</COMMENTS>
            <USERNAME>admin</USERNAME>
            <STUDENT_GRADES>
                <STUDENT_GRADE>
                    <NAME>Science</NAME>
                    <VALUE>77</VALUE>
                    <INHERITED>false</INHERITED>
                </STUDENT_GRADE>
                <STUDENT_GRADE>
                    <NAME>English</NAME>
                    <VALUE>85</VALUE>
                    <INHERITED>false</INHERITED>
                </STUDENT_GRADE>
                <STUDENT_GRADE>
                    <NAME>Spanish</NAME>
                    <VALUE/>
                    <INHERITED>true</INHERITED>
                </STUDENT_GRADE>
                <STUDENT_GRADE>
                    <NAME>SocialStudies</NAME>
                    <VALUE>100</VALUE>
                    <INHERITED>false</INHERITED>
                </STUDENT_GRADE>
            </STUDENT_GRADES>
</Student>

You should have more than 2 tables for this. Creating a column for each class is a very bad design. You should have a table for Classes (English, Math, Greek) and a table for StudentGrades which would have the ClassID, StudentID and grade. This way any student can have any number of grades (and even two for the same class if they take the class a second time). You also don't have to rewrite all your queries and add new columns to your table every time a new class is created. — Sean Lange
– Sean Lange, Commented Jun 24, 2015 at 18:47

jdweng · Accepted Answer · 2015-06-24 21:09:42Z

1

Use the bcp executable that comes with SQL Server rather than to re-invent the wheel. See following webpage : https://msdn.microsoft.com/en-us/library/ms178129.aspx

answered Jun 24, 2015 at 21:09

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

XML to SQL with C#

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related