SqlServer——for xml path

 

for xml path 就是将 sql 查询出来的内容以XML的格式显示出来。参考网站MSDN：将 PATH 模式与 FOR XML 一起使用。

先创建测试用的表格：

  create  table SZQCSBJ
 (
         省 varchar(100),
         市 varchar(100),
         县 varchar(100)
 );
  insert into SZQCSBJ values('山东' ,'潍坊' ,'安丘'  );
  insert into SZQCSBJ values('山东' ,'潍坊','寿光' );
  insert into SZQCSBJ values('山东','济南','长清' );
  insert into SZQCSBJ values('广东','深圳','深圳1');
  insert into SZQCSBJ values('广东','深圳','深圳2');
  insert into SZQCSBJ values( '江苏','苏州','苏州1');
  insert into SZQCSBJ values('江苏','苏州','苏州2');

1、默认格式

　　首先直接执行如下sql：

select * from SZQCSBJ for xml path

生成格式如下：

<row>    --第一行

<列1名称>列1内容</列1名称>  --第一列

<列2名称>列2内容</列2名称>

……

</row>

<row>    --第二行

<列1名称>列1内容</列1名称>  --第一列

<列2名称>列2内容</列2名称>

……

</row>

结果如图：

格式如下：

2、常数，即没有列名，不是从表中查出来的数据的显示格式。

select 1+1 ,2+2 for xml path

结果如下：

可见，当没有列名的时候，就直接省略了列的节点，导致两列连在了一块，成为了一个节点的内容。

3、为行节点指定名称，替换掉默认的row，只需要为path 传递一个参数

select 1+1 ,2+2 for xml path('常量测试')

4、为列指定名称：为列起一个别名

select 1+1 as '列1' ,2+2 as '列2' for xml path('常量测试')

结果如下：

5、行转列,以上面的表格为例。

select 市,left(所辖县,LEN(所辖县)-1)所辖县 from 
(
    select 市,(
            select 县+'、' from  SZQCSBJ where 市=A.市 for XML path('')
        )所辖县
    from SZQCSBJ A
    group by 市
)B

结果如图：

　　

说明：

　　首先执行子查询：select 县+'、' from  SZQCSBJ where 市=A.市 for XML path('')

　　利用外部查询传递的 列 市 的数据，将该市的所有县查询出来，由于 县+'、' 并不是直接从表中查询来的，并且没有起一个列名，所以就没有列相关的节点名。同时将行节点命名为空字符'',所以也没有行节点，得到结果 '长清、',在后续的处理中将最后一个 '、' 字符去掉，就实现了行转列的目的