sql xml xquery (1)

最新推荐文章于 2025-08-28 11:25:33 发布

转载最新推荐文章于 2025-08-28 11:25:33 发布 · 691 阅读

Sql Server 专栏收录该内容

125 篇文章

订阅专栏

本文通过具体示例展示了如何使用 SQL Server 中的 FORXML PATH 函数来生成 XML 格式的字符串。包括创建临时表、插入数据及不同参数下 FORXML PATH 的应用效果，并给出了一种方法来构建带有自定义分隔符的列表。

DECLARE @TempTable table(UserID int , UserName nvarchar(50));   
insert into @TempTable (UserID,UserName) values (1,'a')   
insert into @TempTable (UserID,UserName) values (2,'b')   
select UserID,UserName from @TempTable FOR XML PATH

DECLARE @TempTable table(UserID int , UserName nvarchar(50));   
insert into @TempTable (UserID,UserName) values (1,'a')   
insert into @TempTable (UserID,UserName) values (2,'b')   
select UserID,UserName from @TempTable FOR XML PATH('logic')

DECLARE @TempTable table(UserID int , UserName nvarchar(50));   
insert into @TempTable (UserID,UserName) values (1,'a')   
insert into @TempTable (UserID,UserName) values (2,'b')   
select UserID,UserName from @TempTable FOR XML PATH('')

DECLARE @TempTable table(UserID int , UserName nvarchar(50));   
insert into @TempTable (UserID,UserName) values (1,'a')   
insert into @TempTable (UserID,UserName) values (2,'b') 
select CAST(UserID AS varchar) + '',UserName + '' from @TempTable FOR XML PATH('')

DECLARE @TempTable table(UserID int , UserName nvarchar(50));   
insert into @TempTable (UserID,UserName) values (1,'a')   
insert into @TempTable (UserID,UserName) values (2,'b') 
select CAST(UserID AS varchar) + ',',UserName + '',';' from @TempTable FOR XML PATH('')

DECLARE @TempTable table(UserID int , UserName nvarchar(50));   
insert into @TempTable (UserID,UserName) values (1,'a')   
insert into @TempTable (UserID,UserName) values (2,'b')   
select '{' + CAST(UserID AS varchar) + ',','"' +UserName + '"','}' from @TempTable FOR XML PATH('') 

DECLARE @T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50));   
insert into @T1 (UserID,UserName,CityName) values (1,'a','SHANGHAI')   
insert into @T1 (UserID,UserName,CityName) values (2,'b','BEIJING')   
insert into @T1 (UserID,UserName,CityName) values (3,'c','SHANGHAI')   
insert into @T1 (UserID,UserName,CityName) values (4,'d','BEIJING')   
insert into @T1 (UserID,UserName,CityName) values (5,'e','SHANGHAI')   
SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) as  '用户名' FROM (   
	SELECT CityName,   
	(
		SELECT UserName+',' FROM @T1 WHERE CityName=A.CityName FOR XML PATH('')
	) AS UserList   
	FROM @T1 A    
	GROUP BY CityName   
) B

http://blog.youkuaiyun.com/fireliangbin/article/details/7783611