Ror xml path usage in SQL Server

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.

I. 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 ('')

Okay. Here is a basic introduction to the for xml path,