string concatenation using SQL's for XML path

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