Shulou(Shulou.com)06/01 Report--

For XML PATH is used to display query results in XML form, but through FOR XML PATH can simplify our query statements and achieve some functions that previously needed to be implemented, such as concatenating query results in the form of strings.

1. Convert the query results into XML files. TestTable records are as follows

FID FName FSex

One of three men.

2 Li Si Nan

Three kings and five men.

4 Zhao Xiaohua female

5 Su ×××× Female

6 weeks old girl.

SQL statements containing for xml path are as follows

select FID,FName,FSex from TestTable for xml path

The result of executing SQL statements is

1 Zhang San Male 2 Li Si Male 3 Wang Wu Male 4 Zhao Xiaohua Female 5 Su ××× Female 6 Zhou Xiaomeng Female

2. Use for xml path to realize field splicing

To put together the names of men with gender, the sql statement required is as follows:

select ' '+FName from TestTable where FSex ='male ' for xml path('')

The result of sql statement execution is: Zhang San Li Si Wang Wu. This will achieve the goal of splicing the query results.

3. In sql queries, related subqueries are generally used to realize the sql result set that outputs the concatenated results. If the concatenation of strings uses related characters (|,) is usually performed in conjunction with the stuff function. For more information on how to use the stuff function, please refer to my blog post,"SQL Server stuff function usage."

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.