for xml path 用法
for xml path
sqlserver用法示例
创建表
use dbo;
IF object_id('hobby') is not null drop table hobby;
create table hobby(id int, name varchar(50));
insert into hobby values(1,'爬山');
insert into hobby values(2,'游泳');
insert into hobby values(3,'吃饭');
select * from hobby;
id | name |
---|---|
1 | 爬山 |
2 | 游泳 |
3 | 吃饭 |
select * from hobby for xml path
查询结果为字符串:
<row><id>1</id><name>爬山</name></row><row><id>2</id><name>游泳</name></row><row><id>3</id><name>吃饭</name></row>
格式化后:
<row>
<id>1</id><name>爬山</name>
</row>
<row>
<id>2</id><name>游泳</name>
</row>
<row>
<id>3</id><name>吃饭</name>
</row>
结果
employee | departid | salary |
---|---|---|
2 | 10 | 4500.00 |
1 | 10 | 5500.00 |
3 | 20 | 1900.00 |
4 | 20 | 4800.00 |
5 | 40 | 6500.00 |
6 | 40 | 14500.00 |
7 | 40 | 44500.00 |
8 | 50 | 6500.00 |
9 | 50 | 7500.00 |
select * from hobby for xml path('行')
<行>
<id>1</id><name>爬山</name>
</行>
<行>
<id>2</id><name>游泳</name>
</行>
<行>
<id>3</id><name>吃饭</name>
</行>
变形
SELECT '['+name+']' FROM hobby FOR XML PATH('')
[爬山][游泳][吃饭]
SELECT name+';' FROM hobby FOR XML PATH('')
爬山;游泳;吃饭;
SELECT '{'+STR(id)+'}','['+name+']' FROM hobby FOR XML PATH('')
{ 1}[爬山]{ 2}[游泳]{ 3}[吃饭]
SELECT stuff((SELECT ';'+name FROM hobby FOR XML PATH('')),1,1,'')
爬山;游泳;吃饭