January 15, 2012 at 1:13 pm
Hi, I am trying to import XML data into SQL Server tables using T-SQL FOR XML. The scenario can hypothetically described as follows.
Thanks in advance for your help on this.
I receive an XML file daily with a list of students and the courses they have completed. I don't already have a table with all possible courses, and a student can have any number of courses.
<STUDENTLIST>
<STUDENT>
<NAME>Doe</NAME>
<COURSE>Math</COURSE>
<COURSE>Computers</COURSE>
</STUDENT>
<STUDENT>
<NAME>Rick</NAME>
<COURSE>Science</COURSE>
<COURSE>Math</COURSE>
<COURSE>English</COURSE>
</STUDENT>
</STUDENTLIST>
Data from the XML needs to be written to the following tables:
CREATE TABLE Student
(
StudentID INT IDENTITY(1,1) PRIMARY KEY
, StudentName VARCHAR(25)
) ;
CREATE TABLE Course
(
CourseID INT IDENTITY(1,1) PRIMARY KEY
, CourseName VARCHAR(15)
)
CREATE TABLE Student_Course
(
StudentID INT FOREIGN KEY REFERENCES Student(StudentID)
, CourseID INT FOREIGN KEY REFERENCES Course(CourseID)
)
I'm thinkig, because it is possible to not have courses in the table, i first need to incrementally load course to the course table, then insert students, and then the Student_Couse table.
Thanks,
January 15, 2012 at 3:22 pm
I'd start with first shredding the xml document:
DECLARE @xml XML
SET @xml=
'<STUDENTLIST>
<STUDENT>
<NAME>Doe</NAME>
<COURSE>Math</COURSE>
<COURSE>Computers</COURSE>
</STUDENT>
<STUDENT>
<NAME>Rick</NAME>
<COURSE>Science</COURSE>
<COURSE>Math</COURSE>
<COURSE>English</COURSE>
</STUDENT>
</STUDENTLIST>'
SELECT
T.c.value('(NAME/text())[1]','VARCHAR(30)') AS StudentName,
U.v.value('(./text())[1]','VARCHAR(30)') AS StudentCourse
INTO #temp
FROM @xml.nodes('STUDENTLIST/STUDENT') T(c)
CROSS APPLY T.c.nodes('COURSE') U(v)
Based on that I'd insert the grouped values for student and course into the target tables and finally query against the temp table to fill the Student_Course table.
January 16, 2012 at 1:10 am
Just to add a little detail:
CREATE TABLE Student
(
StudentID INT IDENTITY(1,1) PRIMARY KEY,
StudentName VARCHAR(25) NOT NULL UNIQUE
) ;
CREATE TABLE Course
(
CourseID INT IDENTITY(1,1) PRIMARY KEY,
CourseName VARCHAR(15) NOT NULL UNIQUE
)
CREATE TABLE Student_Course
(
StudentID INT REFERENCES Student(StudentID),
CourseID INT REFERENCES Course(CourseID)
)
CREATE TABLE #temp
(
StudentName varchar(25) NOT NULL,
CourseName varchar(15) NOT NULL,
PRIMARY KEY (StudentName, CourseName)
);
DECLARE @xml xml =
'
<STUDENTLIST>
<STUDENT>
<NAME>Doe</NAME>
<COURSE>Math</COURSE>
<COURSE>Computers</COURSE>
</STUDENT>
<STUDENT>
<NAME>Rick</NAME>
<COURSE>Science</COURSE>
<COURSE>Math</COURSE>
<COURSE>English</COURSE>
</STUDENT>
</STUDENTLIST>
'
INSERT #temp
(StudentName, CourseName)
SELECT
StudentName = S.node.value('(NAME/text())[1]','varchar(25)'),
CourseName = C.node.value('(./text())[1]','varchar(15)')
FROM @xml.nodes('STUDENTLIST/STUDENT') AS S(node)
CROSS APPLY S.node.nodes('COURSE') AS C(node);
-- Add new courses
INSERT dbo.Course
(CourseName)
SELECT
t.CourseName
FROM #temp AS t
EXCEPT
SELECT
c.CourseName
FROM dbo.Course AS c
-- Add new students
INSERT dbo.Student
(StudentName)
SELECT
t.StudentName
FROM #temp AS t
EXCEPT
SELECT
s.StudentName
FROM dbo.Student AS s
-- Add new student/course combinations
INSERT dbo.Student_Course
(StudentID, CourseID)
SELECT
s.StudentID,
c.CourseID
FROM #temp AS t
JOIN dbo.Student AS s ON
s.StudentName = t.StudentName
JOIN dbo.Course AS c ON
c.CourseName = t.CourseName
EXCEPT
SELECT
sc.StudentID,
sc.CourseID
FROM dbo.Student_Course AS sc
SELECT * FROM dbo.Course AS c
SELECT * FROM dbo.Student AS s
SELECT * FROM dbo.Student_Course AS sc
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 16, 2012 at 12:19 pm
Exactly what i was looking for!
Thanks very much for the solution!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply