CODEWITHSUNDEEP
c#sqlasp.netsql-serverxml
Austin
Austin

Reputation: 15

XML to SQL with C#

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>

Upvotes: 0

Views: 80

Answers (1)

jdweng
jdweng

Reputation: 34421

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

Upvotes: 1

Related Questions