SqlServer FOR XML PATH

🏷️ SQL Server

将查询结果集以 XML 形式展现，也可以自定义输出格式

格式

SELECT * FROM TABLENAME FOR XML PATH

改变 XML 行节点的名称

SELECT * FROM TABLENAME FOR XML PATH('MyHobby')

SELECT hobbyID AS 'MyCode', hName AS 'MyName' FROM TABLENAME FOR XML PATH('MyHobby')

自定义输出方式

SELECT '[ '+hName+' ]' FROM TABLENAME FOR XML PATH('')

逗号拼接

SELECT B.sName, ISNULL(STUFF(StuList,1,1,''), '') AS hobby FROM (
  SELECT sName,
    (SELECT ',' + hobby FROM student
      WHERE sName=A.sName
      FOR XML PATH('')) AS StuList
     FROM student A
     GROUP BY sName
    ) B

---

原文：http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

---