如何通过SQL Server查询获取指定格式的XML数据？

需要编写SQL Server查询脚本，从给定的school表中生成符合指定格式的XML数据。

表结构与测试数据 #

CREATE TABLE school
(
    class int,
    section varchar(1),
    studentName varchar(50)
);

INSERT INTO school (class, [section], studentName)
VALUES (1, 'A', 'Mahesh'),
       (1, 'A', 'Jack'),
       (1, 'A', 'Kismo'),
       (1, 'B', 'Hari'),
       (1, 'B', 'Khan');

目标XML格式 #

<listResponse Section="A" count="3">
    <instance class="1" studentName="Mahesh"/>
    <instance class="1" studentName="Jack"/>
    <instance class="1" studentName="Kismo"/>
</listResponse>

实现查询语句 #

以下查询可生成与示例完全一致的XML格式，支持按section分组输出：

SELECT 
    s.section AS '@Section',
    COUNT(s.studentName) AS '@count',
    (
        SELECT 
            class AS '@class',
            studentName AS '@studentName'
        FROM school s2
        WHERE s2.section = s.section
        FOR XML PATH('instance'), TYPE
    )
FROM school s
WHERE s.section = 'A' -- 可替换为需要查询的section，如'B'
GROUP BY s.section
FOR XML PATH('listResponse'), TYPE;

说明 #

1. 外层查询通过GROUP BY按section分组，计算分组内学生数量作为count属性，同时指定根节点listResponse的Section属性。
2. 内层子查询针对当前分组的section，生成每个学生对应的instance节点，并通过FOR XML PATH('instance')将结果嵌套到根节点中。
3. 若需要生成所有section的XML结果，移除WHERE s.section = 'A'条件即可。

内容的提问来源于stack exchange，提问作者Dheeraj