SQL SERVER for XML PATH

Turn from: Wang Poyang (PS: The current blog has been read, feel good, originally planned to write, then it is not enough time and Bo master write good, so turn around and slightly revised under)

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.

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>  <Hobbyid>1</Hobbyid>  <Hname>Climbing</Hname></Row><Row>  <Hobbyid>2</Hobbyid>  <Hname>Swimming</Hname></Row><Row>  <Hobbyid>3</Hobbyid>  <Hname>Food</Hname></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>  <Hobbyid>1</Hobbyid>  <Hname>Climbing</Hname></Myhobby><Myhobby>  <Hobbyid>2</Hobbyid>  <Hname>Swimming</Hname></Myhobby><Myhobby>  <Hobbyid>3</Hobbyid>  <Hname>Food</Hname></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 " " 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) + ' } ",< Span style= "COLOR: #ff0000" > ' [  ' +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:
　　　　

SELECTB.sname, Left(Stulist,LEN(stulist)-1) asHobby from (SELECTSName, (SELECTHobby+','  fromStudentWHERESName=A.sname forXML PATH ("')) asstulist fromStudent AGROUP  bysName) 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:

SELECTB.sname, Left(Stulist,LEN(stulist)-1) asHobby from (SELECTSName, (SELECTHobby+','  fromStudentWHERESName=A.sname forXML PATH ("')) asstulist fromStudent AGROUP  bysName) 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, and 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 note!

Three. The actual production of the application is the query only need to view the main table, but also need some important information column of the Word table can use this technique.

Reference blog:

"1" Flexible use of SQL SERVER for XML PATH: http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

Testimonials: Thank the seniors for sharing their learning results.

SQL SERVER for XML PATH