for xml path

摘自: https://www.cnblogs.com/KeepHoist/p/16133573.html

Sql server—— for xml path简单用法
FOR XML PATH ，其实它就是将查询结果集以XML形式展现，将多行的结果，展示在同一行。

FOR XML子句有四种最基本的模式：

　　1、AUTO模式：返回数据表为起表名的元素，每一列的值返回为属性；

　　2、RAW模式：返回数据行为元素，每一列的值作为元素的属性；

　　3、PATH模式：通过简单的XPath语法来允许用户自定义嵌套的XML结构、元素、属性值

　　4、EXPLICIT模式：通过SELECT语法定义输出XML的结构

数据表——

CREATE TABLE [dbo].[tb](
[id] varchar NULL,
[pid] varchar NULL,
[name] varchar NULL
) ON [PRIMARY]

GO

插入数据——

--创建表，插入数据

insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go

for xml path简单用法（可以按照分组把相同组的列中的不同的值，像字符串一样拼接在一起显示在分组之后的列中。）
查询——

   1.把所有地区名称集中显示

   select  ','+name from tb  FOR xml path('') 

   结果如下：

2. 根据pid 相同的字段分组，同一pid的地区名称合并一起，并去重显示

   SELECT  distinct  s.pid, (SELECT ',' + name FROM tb WHERE pid = s.pid FOR xml path (''))  as name FROM  tb s
   
     结果如下：（显示内容第一字符会带有 ’，‘ 逗号）
   ![](https://img2024.cnblogs.com/blog/76253/202506/76253-20250604165919007-1117877078.png)
   

   3. 通过stuff 函数将第一字符的逗号去掉

   SELECT distinct s.pid, (stuff((SELECT ',' + name FROM tb WHERE pid = s.pid FOR xml path ('')),1,1,'')) as name FROM tb s

     结果如下：
   

   

*如果逗号是显示在最末尾处，如下:

     SELECT pid,(select name+',' from tb where tb.pid=a.pid FOR xml path('') ) AS Sname   FROM tb A   GROUP BY A.pid         

    则通过以下处理：
    SELECT pid,LEFT(Sname,LEN(Sname)-1) as name FROM (
      SELECT pid, (select name+',' from tb where tb.pid=a.pid FOR xml path('') ) AS Sname
      FROM tb A
      GROUP BY A.pid
    ) B

4.排序

SELECT pid AS '@bldid', 'true' AS '@checked',(SELECT name AS 'roomid'FROM tb WHERE (tb.pid=a.pid) FOR XML PATH (''), TYPE)
FROM tb a
GROUP BY pid
FOR XML PATH ('build'), root('tjrooms')

如下：

将数据导出到XML文件
创建表——

create table xmltab (xtable varchar(max))

执行导出——
declare @strsql VARCHAR(4000)
declare @strPath VARCHAR(100)
declare @str VARCHAR(200)
SELECT @str= convert(varchar(12),getdate()-1,112)
SET @strPath='D:\XMLOutFile'
SET @strPath+='B_'+@str
SET @strPath+='.XML' insert into TEST..xmltab values('')
declare @x xml
set @x=( select pid, isnull(name,'''') AS rname from test..tb FOR XML PATH('roots'),TYPE , ELEMENTS ,ROOT('rootp')) insert into test..xmltab select cast(@x as varchar(max))
SET @strsql='bcp test.dbo.xmltab out '
SET @strsql+=@strPath
SET @strsql+=' -c -T -k -x'
exec master..xp_cmdshell @strsql