SQL Server for XML Path

SQL Server for XML Path some may not know others. In fact, it shows the query result set in XML format, with this feature, we can simplify our query statements to implement some work that may previously need to be completed through the function live storage process. Take an instance as the main type.

1. for XML Path

First, let's introduce the for XML Path. Suppose there is a hobby table used to store hobbies. The table structure is as follows:

Next, let's look at the Query Result Statement using the for XML Path:

Select * From @ holobby for XML Path

Result:

<Row>
<Hobbyid> 1 <Hname> mountain creation </Row>
<Row>
<Hobbyid> 2 <Hname> swimming </Row>
<Row>
<Hobbyid> 3 <Hname> food </Row>

From this we can see that for XML Path can output query results into various XML types based on rows!

How can I change the name of an XML row node? The Code is as follows:

Select * From @ holobby for XML Path ('myhobby ')

The results can be imagined, right? The original row node <row> is changed to the custom name <myholobby> in the brackets () behind path. The result is as follows:

<Myholobby>
<Hobbyid> 1 <Hname> mountain creation </Myholobby>
<Myholobby>
<Hobbyid> 2 <Hname> swimming </Myholobby>
<Myholobby>
<Hobbyid> 3 <Hname> food </Myholobby>

At this time, careful friends will certainly ask how the column nodes are changed? Do you still remember the as keyword for the column alias? By the way, use it! The Code is as follows:

Select hobbyid as 'mycode', hname as 'myname' from @ holobby for XML Path ('myhobby ')

At this time, the node names in our column will also program our custom names <mycode> and <myname>. The results are as follows:

<Myholobby>
<Mycode> 1 </mycode>
<Myname> mountain creation </myname>
</Myholobby>
<Myholobby>
<Mycode> 2 </mycode>
<Myname> swimming </myname>
</Myholobby>
<Myholobby>
<Mycode> 3 </mycode>
<Myname> food </myname>
</Myholobby>

Oh! Since we can customize the row and column nodes, can we build our favorite output methods? Check the Code:

Select '[' + hname + ']' from @ holobby for XML Path ('')

Yes, we can also define the output format of string fields by symbol +. The result is as follows:

[Hiking] [Swimming] [food]

How can I customize columns of other types? It doesn't matter. Just convert them to the string type! For example:

Select '{' + STR (hobbyid) + '}', '[' + hname + ']' from @ holobby for XML Path ('')

A good for XML Path is basically introduced here. For more information about for XML, please refer to the help documentation!

Next, let's look at an Application Scenario for XML Path! Let's get started ......

2. One Application scenario and the for XML Path Application

First! We are adding a student table with the following columns: (stuid, sname, holobby), stuid, sname, and Hober! The table structure is as follows:

In this case, we want to query the student table and display the result set of all students' hobbies. The Code is as follows:

Select B. sname, left (stulist, Len (stulist)-1) as holobby from (
Select sname,
(Select holobby + ',' from student
Where sname = A. sname
For XML Path ('') as stulist
From student
Group by sname
) B

The result is as follows:

Analysis:Okay, let's analyze it. First, let's look at this sentence:

Select holobby + ',' from student
Where sname = A. sname
For XML Path ('')

This example shows the hobby of a name such as John through the for XML Path in the format of "hobby 1, hobby 2, hobby 3!

Then let's see:

Select B. sname, left (stulist, Len (stulist)-1) as holobby from (
Select sname,
(Select holobby + ',' from student
Where sname = A. sname
For XML Path ('') as stulist
From student
Group by sname
) B

The remaining code first groups the table and formatting the for XML Path. When the SELECT statement of the outermost layer is not executed, the query structure is as follows:

We can see that the data in the stulist column will contain an extra Comma. Then, with the outer statement: Select B. sname, left (stulist, Len (stulist)-1) is to remove the comma and assign meaningful listing!

For XML Path some people may not know. In fact, it shows the query result set in XML format, with this feature, we can simplify our query statements to implement some work that may previously need to be completed through the function live storage process. Take an instance as the main type.

1. for XML Path

First, let's introduce the for XML Path. Suppose there is a hobby table used to store hobbies. The table structure is as follows:

Next, let's look at the Query Result Statement using the for XML Path:

Select * From @ holobby for XML Path

Result:

<Row>
<Hobbyid> 1 <Hname> mountain creation </Row>
<Row>
<Hobbyid> 2 <Hname> swimming </Row>
<Row>
<Hobbyid> 3 <Hname> food </Row>

From this we can see that for XML Path can output query results into various XML types based on rows!

How can I change the name of an XML row node? The Code is as follows:

Select * From @ holobby for XML Path ('myhobby ')

The results can be imagined, right? The original row node <row> is changed to the custom name <myholobby> in the brackets () behind path. The result is as follows:

<Myholobby>
<Hobbyid> 1 <Hname> mountain creation </Myholobby>
<Myholobby>
<Hobbyid> 2 <Hname> swimming </Myholobby>
<Myholobby>
<Hobbyid> 3 <Hname> food </Myholobby>

At this time, careful friends will certainly ask how the column nodes are changed? Do you still remember the as keyword for the column alias? By the way, use it! The Code is as follows:

Select hobbyid as 'mycode', hname as 'myname' from @ holobby for XML Path ('myhobby ')

At this time, the node names in our column will also program our custom names <mycode> and <myname>. The results are as follows:

<Myholobby>
<Mycode> 1 </mycode>
<Myname> mountain creation </myname>
</Myholobby>
<Myholobby>
<Mycode> 2 </mycode>
<Myname> swimming </myname>
</Myholobby>
<Myholobby>
<Mycode> 3 </mycode>
<Myname> food </myname>
</Myholobby>

Oh! Since we can customize the row and column nodes, can we build our favorite output methods? Check the Code:

Select '[' + hname + ']' from @ holobby for XML Path ('')

Yes, we can also define the output format of string fields by symbol +. The result is as follows:

[Hiking] [Swimming] [food]

How can I customize columns of other types? It doesn't matter. Just convert them to the string type! For example:

Select '{' + STR (hobbyid) + '}', '[' + hname + ']' from @ holobby for XML Path ('')

A good for XML Path is basically introduced here. For more information about for XML, please refer to the help documentation!

Next, let's look at an Application Scenario for XML Path! Let's get started ......

2. One Application scenario and the for XML Path Application

First! We are adding a student table with the following columns: (stuid, sname, holobby), stuid, sname, and Hober! The table structure is as follows:

In this case, we want to query the student table and display the result set of all students' hobbies. The Code is as follows:

Select B. sname, left (stulist, Len (stulist)-1) as holobby from (
Select sname,
(Select holobby + ',' from student
Where sname = A. sname
For XML Path ('') as stulist
From student
Group by sname
) B

The result is as follows:

Analysis:Okay, let's analyze it. First, let's look at this sentence:

Select holobby + ',' from student
Where sname = A. sname
For XML Path ('')

This example shows the hobby of a name such as John through the for XML Path in the format of "hobby 1, hobby 2, hobby 3!

Then let's see:

Select B. sname, left (stulist, Len (stulist)-1) as holobby from (
Select sname,
(Select holobby + ',' from student
Where sname = A. sname
For XML Path ('') as stulist
From student
Group by sname
) B

The remaining code first groups the table and formatting the for XML Path. When the SELECT statement of the outermost layer is not executed, the query structure is as follows:

We can see that the data in the stulist column will contain an extra Comma. Then, with the outer statement: Select B. sname, left (stulist, Len (stulist)-1) is to remove the comma and assign meaningful listing!