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](3) NULL,
[pid] [varchar](3) NULL,
[name] [varchar](10) 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

          结果如下：（显示内容第一字符会带有 ’，‘ 逗号）

        

 

      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('<?xml version="1.0" encoding="GB2312"?>')
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