如何使存储过程查询输出指定结构的XML格式？

原始查询语句：

SELECT 
    IncidentEventID AS Id, -- Always select first column as the primary key column and use alias as Id 
    CreatedDate,
    IncidentEventName,
    IncidentEventDesc
FROM
    vIncidentEvent
WHERE
    IncidentID = 987148059
    AND AuditTypeID IS NULL;

查询返回结果：

需要输出的目标XML结构：

<xml>
  <rows>
    <row id="121144">
      <cell>
        <![CDATA[8/2/2024 11:32:57 AM]]>
      </cell>
      <cell>
        <![CDATA[Request Initiated]]>
      </cell>
      <cell>
        <![CDATA[RC_GA_BASELINE NBI initiated by Admin, LBMS]]>
      </cell>
    </row>
    <row id="121145">
      <cell>
        <![CDATA[8/2/2024 11:38:17 AM]]>
      </cell>
      <cell>
        <![CDATA[Task Completed]]>
      </cell>
      <cell>
        <![CDATA[Enter Parties and Key Info completed by Admin, LBMS]]>
      </cell>
    </row>
  </rows>
</xml>

用户尝试的错误查询：

SELECT 
    IncidentEventID AS [@id], -- Alias the primary key as an XML attribute
    CreatedDate AS 'cell',
    IncidentEventName AS 'cell',
    IncidentEventDesc AS 'cell'
FROM
    vIncidentEvent
WHERE
    IncidentID = 987148059
    AND AuditTypeID IS NULL
FOR XML PATH('row'), ROOT('rows'), TYPE;

问题：所有字段内容被合并到同一个<cell>标签内，不符合需求。

问题根源是直接将多个字段命名为cell，SQL Server会自动合并同名元素。要生成独立的<cell>标签，需为每个字段创建单独的嵌套结构，同时手动拼接CDATA以匹配目标格式，最后包裹外层<xml>节点。

正确的查询语句如下：

SELECT (
    SELECT 
        IncidentEventID AS [@id],
        -- 为每个字段生成独立cell标签并包裹CDATA
        (SELECT '<![CDATA[' + CONVERT(VARCHAR(50), CreatedDate, 101) + ' ' + CONVERT(VARCHAR(10), CreatedDate, 108) + ']]>' AS [*] FOR XML PATH(''), TYPE) AS cell,
        (SELECT '<![CDATA[' + IncidentEventName + ']]>' AS [*] FOR XML PATH(''), TYPE) AS cell,
        (SELECT '<![CDATA[' + IncidentEventDesc + ']]>' AS [*] FOR XML PATH(''), TYPE) AS cell
    FROM vIncidentEvent
    WHERE IncidentID = 987148059 AND AuditTypeID IS NULL
    FOR XML PATH('row'), ROOT('rows'), TYPE
) AS [*]
FOR XML PATH('xml'), TYPE;

关键说明： #

• 独立cell标签：通过子查询(SELECT ... FOR XML PATH(''), TYPE)为每个字段生成独立内容块，再指定别名cell，确保每个字段对应单独的<cell>节点。
• CDATA包裹：手动拼接<![CDATA[...]]>字符串，避免特殊字符转义；日期字段用CONVERT格式化，保证输出格式与原始结果一致。
• 外层xml节点：最外层子查询将<rows>结果作为内容，通过FOR XML PATH('xml')生成顶层<xml>节点。

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