This article is mainly about how to use the SQL for XML path parameter for string concatenation , the use of the for XML path is very simple, It returns data in the form of an XML file.
My steps to explain:
1: Construct initial data 2: Ask question 3: Brief introduction
for
XML
PATH4: Answer the question
1. Construct the initial data
Give an example of a classic student course, with three tables for students, courses and student courses.
Table 1:student
student_id |
Student_name |
1 |
Tom |
2 |
John doe |
3 |
Harry |
Table 2:course
course_id |
Course_name |
1 |
Language |
2 |
Mathematical |
3 |
English |
Table 3:student_course
student_id |
course_id |
1 |
2 |
1 |
3 |
2 |
1 |
2 |
3 |
3 |
3 |
Script:
CreateTableStudent (student_idIntPrimaryKey, Student_namenvarchar(50)NotNull)
CreateTableCourse (course_idIntPrimaryKey, Course_namenvarchar(50)NotNull)
create table< Span style= "color: #000000;" > student_course ( student_id int not null int not null primary key (student_id,course_id))
2. Ask questions
Write an SQL statement, and the query displays the following results:
Student_name |
Course_name |
Tom |
Math, English |
John doe |
Language, English |
Harry |
English |
3. Brief introduction
for
XML
PATH
forThe
XML
PATH statement can generate
XML data for the queried data , for example, for the student table, the query result of the previous SQL statement is:
Select str(student_id) + ',' + student_name from student C14>for XML Path ('student')
Query Result:
<Student> 1, Zhang San </student>< Span style= "color: #000000;" > <student>< Span style= "color: #000000;" >&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;2, John Doe </student> << Span style= "color: #800000;" >student> 3, Harry </student>
Student has become a node in an XML file, and then look at the effect of the for XML PATH("), making changes to the above SQL .
Select str(student_id) + ',' + student_name from student C14>for XML Path (")
Query Result:
1, Zhang San 2, John Doe 3, Harry
It can be seen that this parameter automatically strings our query results together, this, to do string concatenation is very simple!
4. Answer the question to query the desired result, we first use a general SQL statement, the result of connecting three tables is:
Select a.student_name,b.course_name from student_course c,student a,course b where c. student_id=a.student_id and c.course_id=b.course_id
Query Result:
Student_name |
Course_name |
Tom |
Mathematical |
Tom |
English |
John doe |
Chinese |
John doe |
English |
Harry |
English |
We look at this query result as a temporary table, a connection with itself, and then with the for XML PATH(") parameter to the course course_name column to splice, Then you have to use the subquery function. This will result in a selection of all the courses for each student, since there are multiple records for the same student in the table above, so you need to group the final results by student, first look at the query statement:
SelectStudent_name, (SelectCourse_name+‘,‘From(SelectStudent_name,course_nameFrom(SelectA.student_name,b.course_nameFromStud_course c,student A,course bwherec.student_id=a.student_idandc.course_id=B.COURSE_ID)AsAAsBwhereC.student_name=B.student_nameForXML Path (‘‘) )AsCourse_namefrom ( select a.student_name,b.course_name from student_course c,student a,course b C6>where c.student_id=a.student_id and c.course_id=b.course_id) as C Group by Student_name
Query Result:
Student_name |
Course_name |
Tom |
Math, English, |
John doe |
Languages, English, |
Harry |
English |
There is a small problem, course_name after a number, the last to do a clipping, assuming the above SQL statement as a subquery subquery
Select student_name,left(course_name,len(course_name)–1) from ( ........) As subquery
In this way, you can draw the final result! It can be seen that the for XML PATH(") parameter is very powerful!
string concatenation using SQL's for XML path