ROR XML PATH usage in SQL Server

FOR XML PATH Some people may know that some people may not know, in fact, it is the query result set in the form of XML, with which we can simplify our query to implement some of the work that might previously need to be done with the help of a function live stored procedure. Then take one instance as the main.

One. For XML PATH Brief introduction

So let's start with the for XML PATH, assuming that there's a hobby table (hobby) that you can use to store your hobbies, and the table structure is as follows:

Next we look at the query result statement that applies for XML path as follows:

SELECT * from @hobby for XML PATH

Results:

<row>

</row>

<row>

</row>

<row>

</row>

This shows that for XML PATH can output query results from rows to XML!

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

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

The result must be imagined, right? Yes, that's right. The original row node <row> became us in the path behind the parentheses (), the custom name <myhobby> 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 also remember the keyword as to which you listed the alias? That's right, use it! The code is as follows:

SELECT Hobbyid as ' Mycode ', hname as ' myname ' from @hobby for XML PATH (' Myhobby ')

So this time our list of node names will also be programmed by 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! Since the node of the row and the node of the column can be customized, can we build the output we like? Or look at the code:

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

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

Climbing Swimming Food

So how can other types of columns be customized? It's okay, we'll convert them to string types. For example:

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

OK for XML path is basically about here, for more information on for XML please consult the Help documentation!

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

Two. An application scenario and for XML path application

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

At this time, our request is to query the student table, showing the interests of all students of the result set, 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, first of all look at this sentence:

SELECT hobby+ ', ' from student

WHERE Sname=a.sname

For XML PATH (')

This is through for XML PATH to a name such as John's hobby, displayed in the format: "Hobby 1, Hobby 2, Hobby 3," Format!

So then look:

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 is to group the tables first, in the execution for XML PATH format, and when the outermost select is not yet executed, the structure of the query is:

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