SQL Server中如何生成含DATA属性节点的指定XML结构

需求说明 #

需将表中每行的date和name字段作为<DATA>元素的属性，所有<DATA>节点统一放在单个<EventList>节点下，最终外层包裹<Events>根节点。

示例数据 #

CREATE TABLE testTable(
  date datetime,
  name varchar(50)
);
  
INSERT INTO testTable (date, name)
VALUES ('1901-01-01 00:00:00', 'a'), ('1902-02-02 00:00:00', 'b')

原查询及问题 #

原查询语句：

select * from testTable FOR XML path('EventList'), ROOT ('Events')

该查询会为每行数据生成一个独立的<EventList>节点，且字段以子元素形式呈现，不符合需求。

当前输出：

<Events>
  <EventList>
    <date>1901-01-01 00:00:00</date>
    <name>a</name>
  </EventList>
  <EventList>
    <date>1902-02-02 00:00:00</date>
    <name>b</name>
  </EventList>
</Events>

期望输出 #

<Events>
  <EventList>
    <DATA date="1901-01-01 00:00:00" name="a" />
    <DATA date="1902-02-02 00:00:00" name="b" />
  </EventList>
</Events>  

解决方案 #

通过嵌套FOR XML PATH查询实现需求：

SELECT
  (SELECT 
     date AS '@date',
     name AS '@name'
   FROM testTable
   FOR XML PATH('DATA'), TYPE) AS EventList
FOR XML PATH(''), ROOT('Events')

关键说明 #

• 内层子查询：用AS '@字段名'将字段转换为<DATA>元素的属性，PATH('DATA')指定每行生成一个<DATA>节点，TYPE确保结果以XML类型返回，避免被自动转义。
• 外层查询：将内层生成的XML集合作为<EventList>节点的内容，PATH('')避免外层生成额外的默认节点，最后通过ROOT('Events')添加根节点。

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