Flexible use of SQL SERVER for XML PATH

FOR XML PATH Some people may know that some people may not know, in fact, it is the query result set is presented in XML form, with it we can simplify our query statement implementation of some previously may need to rely on the function of a live stored procedure to complete the work. Then take one instance as the main.

A. For XML PATH Brief introduction

So let's start with the for XML PATH, assuming that there is now a hobby table (hobby) to store interests, the table structure is as follows:

Next, let's look at the query result statement that applies the for XML path as follows:

SELECT * from @hobby for XML PATH

Results:

<row>
</row>
<row>
</row>
<row>
</row>

This shows that the FOR XML PATH can output query results according to the row into XML all kinds!

So, how do you change the name of the XML row node? The code is as follows:

SELECT * from @hobby for XML PATH (' Myhobby ')

The results must have been imagined, right? That's right. The original line node <row> became our custom name <myhobby> in parentheses () after the path, and the result is as follows:

<MyHobby>
</MyHobby>
<MyHobby>
</MyHobby>
<MyHobby>
</MyHobby>

This time the careful friend must ask then how does the column node change? Do you remember the keyword as that lists the aliases? Yes, that's it! The code is as follows:

SELECT Hobbyid as ' MyCode ', hname as ' MyName ' from @hobby for XML PATH (' Myhobby ')

So at this point the node name of our column will also be programmed with our custom name <MyCode> and <MyName> results as follows:

<MyHobby>
<MyCode>1</MyCode>
<MyName> Climbing </MyName>
</MyHobby>
<MyHobby>
<MyCode>2</MyCode>
<MyName> Swimming </MyName>
</MyHobby>
<MyHobby>
<MyCode>3</MyCode>
<MyName> Food </MyName>
</MyHobby>

Oh! Now that we can customize the nodes and columns of the row, can we build the output that we like? Or look at the code:

SELECT ' [' +hname+ '] ' from @hobby for XML PATH (')

Yes, we can also define the output format of a String type field by using the symbol + number. The results are as follows:

Climbing Swimming Food

So how do other types of columns customize? That's OK, let's convert them to string type! For example:

SELECT ' {' +str (Hobbyid) + '} ', ' [' +hname+ '] ' from @hobby for XML PATH (')

Good for XML path is basically introduced here, more about the FOR XML knowledge please consult the Help document!

Let's take a look at an application scenario for XML path! So let's start ...

Two. One application scenario with the for XML path application

First of all! We are adding a student table, the column is (Stuid,sname,hobby), Stuid on behalf of the student number, SName on behalf of the student name, hobby the student's hobby! So now the table structure is as follows:

At this point, our request is to query the student table, showing the result set of all students ' hobbies, the code is as follows:

SELECT B.sname,left (Stulist,len (stulist)-1) as Hobby from (
SELECT SName,
(SELECT hobby+ ', ' from student
WHERE Sname=a.sname
For XML PATH (")) as Stulist
From student A
GROUP by SName
) B

The results are as follows:

Analysis: OK, then let's analyze it, first look at this sentence:

SELECT hobby+ ', ' from student
WHERE Sname=a.sname
For XML PATH (")

This sentence is through the for XML PATH to a name like Zhang San's hobby, shown in the format of: "Hobby 1, Hobby 2, Hobby 3," the format!

Then look at:

SELECT B.sname,left (Stulist,len (stulist)-1) as Hobby from (
SELECT SName,
(SELECT hobby+ ', ' from student
WHERE Sname=a.sname
For XML PATH (")) as Stulist
From student A
GROUP by SName
) B

The rest of the code begins by grouping tables, executing the for XML PATH format, when the structure that is queried when the outermost select is not executed is:

You can see that the data in the Stulist column will have a comma, then the statement with the outer layer: SELECT b.sname,left (Stulist,len (stulist)-1) as hobby is to remove the comma, and give a meaningful listing!

Flexible use of SQL SERVER for XML PATH