SQLServer中sql for xml path 的用法

我们通常需要获取一个多行的某个字段拼出的字符串，我们可以使用for xml path进行处理；下面将介绍for xml path的具体用法；

创建测试表&插入测试数据

在数据库中新增测试表

CREATE TABLE MyTest(
  UserName VARCHAR(50),
  Email VARCHAR(100)
)

插入测试数据

INSERT INTO MyTest
        ( UserName, Email )
VALUES  ( '张三', -- UserName - varchar(50)
          'zhangsan@163.com'  -- Email - varchar(100)
          )
INSERT INTO MyTest
        ( UserName, Email )
VALUES  ( '李四', -- UserName - varchar(50)
          'lisi@163.com'  -- Email - varchar(100)
          )

数据库有如下两条数据

 

for xml path使用

普通用法

创建根节点为user的xml

SELECT UserName ,
Email FROM MyTest FOR XML PATH('User')

运行结果：

<User>
  <UserName>张三</UserName>
  <Email>zhangsan@163.com</Email>
</User>
<User>
  <UserName>李四</UserName>
  <Email>lisi@163.com</Email>
</User>

自定义节点名称

给对应的字段取别名即可，如下将UserName取别名为MyUser

SELECT UserName AS MyUser ,
Email  FROM MyTest FOR XML PATH('User')

运行结果

<User>
  <MyUser>张三</MyUser>
  <Email>zhangsan@163.com</Email>
</User>
<User>
  <MyUser>李四</MyUser>
  <Email>lisi@163.com</Email>
</User>

将字段作为xml的属性

 可在查询时 别名用 as　＇＠．．＇　如'@value'

SELECT UserName AS '@UserName' ,
Email AS '@Email'  FROM MyTest FOR XML PATH('User')

运行结果

<User UserName="张三" Email="zhangsan@163.com" />
<User UserName="李四" Email="lisi@163.com" />

行转列拼接字符串

如将所有姓名拼接为，隔开的字符串

SELECT UserName+',' FROM MyTest FOR XML PATH('')

结果

张三,李四,

这里多了一个，需要再次加工处理一下，通过处理字符串将最后一个逗号去掉即可，可通过SQL中的left函数处理，这里不做介绍；

具体用法可参考我的另一篇文章----SQL Server 中截取字符串常用的函数