FOR XML PATH('') 的简单使用

select  Name + ','  from  A  FOR XML PATH('')

若 Name不是 varchar类型，先转

select  CAST(Name AS varchar) + ','  from  A  FOR XML PATH('')

去，号

select left(a,len(a)-1) from  (select  (select  CAST(Name AS varchar) + ','  from A FOR XML PATH('')) a)b

---

 

 接下来我们利用STUFF结合FOR XML PATH来拼接JSON字符串，如下

DECLARE @content VARCHAR(MAX)

SET @content = (SELECT '['+ STUFF((SELECT TOP 5 ',{"ID": "' +  CONVERT(VARCHAR, ID) + '","Years": "' + CONVERT(VARCHAR, Years) + '","Name": "' + CONVERT(VARCHAR, Name) + '","ZipCode": "' + CONVERT(VARCHAR, ZipCode, 105) + '"}' FROM CMS_InfoPublish_N_ServiceObject_Type_People 
 FOR XML PATH('')), 1, 1,''
 ) 
 + ']')

PRINT @content 

补充：

更多stuff用法参考https://www.cnblogs.com/CreateMyself/p/9058380.html

更多FOR XML PATH用法参考https://www.cnblogs.com/yasuo2/p/6433697.html