准备好的表及数据(Customer表)
1、FOR XML PATH应用SELECT * FROM dbo.Customer FOR XML PATH ('Customer')
结果:将查询到表中所有行以及字段拼接成一个xml结构的数据,每行是一个节点(row),字段是xml节点下的元素
2、FOR XML PATH应用无节点输出SELECT * FROM dbo.Customer FOR XML PATH('')
结果:将查询到表中所有行以及字段拼接成一个xml结构的数据,这里与上面的差异在于没有节点(row),是直接将字段连续拼接成xml的
3、FOR XML PATH应用自定义xml节点名称SELECT * FROM dbo.Customer FOR XML PATH ('Customer')
结果:从结果可以看出,这里执行的结果用Customer代替了默认的row节点名称
4、FOR XML PATH应用自定义元素名称SELECT Id AS CusId ,Name CusName FROM dbo.Customer FOR XML PATH ('Customer')
结果:此语句与上面的对比就是我们将xml元素名称通过as的方式修改成了我们自定义别名的元素名称了
5、FOR XML PATH应用,我们通过通配符可以实现无xml结构的字符串拼接效果SELECT CAST(Id AS VARCHAR(50)) + ',' FROM dbo.Customer FOR XML PATH ('')SELECT '{' + CAST(Id AS VARCHAR(50)) + '},[' + Name + '],' FROM dbo.Customer FOR XML PATH ('')
结果:从结果可以看出我们输出的信息中并没有xml标签了
6、FOR XML PATH应用,使用通配符拼接的结果我们也可以使用节点来区分每行的结果SELECT '{' + CAST(Id AS VARCHAR(50)) + '},[' + Name + ']' FROM dbo.Customer FOR XML PATH ('Node')
结果:从输出中我们可以看到我们用node节点来分割出每行的数据了
7、FOR XML PATH应用,从上面的结果我们可以看出输出的列表的名称都是很长的一段编码,我们也可以通过别名的方式来决定我们列的名称SELECT(SELECT CAST(Id AS VARCHAR(50)) + ',' FROM dbo.Customer FOR XML PATH ('')) AS Node
结果:我们用Node代替了XML_F52E2B61-18A1-11d1-B105-00805F49916B
总结:我们使用for xml path可以将我们查询到的结果汇总成一个字段,多行多字段以一个字段输出结果,我们可用通过select来控制字段的组成和xml的元素名称
下面以一个列子来应用一下for xml path(再准备两张表)
User人员表
UserCustomerRelation人员客户关联表
SELECT U.Name,
U.Alias,
U.Position,
STUFF((
SELECT ',' + C.Name
FROM dbo.Customer AS C
WHERE C.Id IN (
SELECT CustomerId FROM dbo.UserCustomerRelation WHERE UserId = U.Id
)
FOR XML PATH('')
),1,1,'') AS CustomerName
FROM dbo.[User] AS U
这里用到了一个STUFF函数,这个函数是用来截取字符串的长度的,使用方法可参见【SQL Server截取字符串的几种方法】