SQL Server 2016生成嵌套XML：消除重复Header标签的实现需求

云原生阿岚

2026-6-18

SQL Server生成指定嵌套XML结构的解决方案

环境信息

使用的SQL Server版本：

Microsoft SQL Server 2016 (SP1-GDR) (KB4458842) - 13.0.4224.16 (X64)   
Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)

需求说明

需要生成嵌套XML文件，满足以下结构要求：

根标签为monthlydata
monthlydata内仅包含一个Header标签
所有coredata标签必须嵌套在该Header标签内部

表结构

CREATE TABLE [dbo].[f_XML_Data]
(
    [SurveyID] [VARCHAR](15) NOT NULL,
    [RecDate] [VARCHAR](15) NOT NULL,
    [DisDate] [VARCHAR](15) NOT NULL,
    [CMS_10] [VARCHAR](50) NULL,
    [CMS_11] [VARCHAR](50) NULL,
    [CMS_23] [VARCHAR](50) NULL,
    [CMS_24] [VARCHAR](50) NULL,
    [CMS_27] [VARCHAR](50) NULL,
    [CMS_30] [VARCHAR](50) NULL,
    [ITADMSRC] [VARCHAR](50) NULL,
    [ITDCGSTA] [VARCHAR](50) NULL,
    [ITDISDAT] [VARCHAR](50) NULL,
    [ITHCAHPS] [VARCHAR](50) NULL,
    [ITSERVIC] [VARCHAR](50) NULL
)

测试数据

INSERT INTO dbo.f_XML_Data VALUES ('6561942362','2024-07-10','2024-03-12','Always','Always','8','Definitely yes','Some college','English','1','6','2024-03-12','220090','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6705168282','2024-07-15','2024-04-10','Always','Always','9','Definitely yes','Some college','English','1','3','2024-04-10','220091','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6866086131','2024-07-02','2024-05-10','Always','Always','9','Definitely yes','4-yr coll. grad.','English','1','1','2024-05-10','220092','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6903690620','2024-07-02','2024-05-16','Usually','Always','9','Definitely yes','4-yr coll. grad.','English','1','1','2024-05-16','220093','Removal')
INSERT INTO dbo.f_XML_Data VALUES ('6978329252','2024-07-09','2024-05-21','Always','Always','10-Best possible','Definitely yes','Some college','English','1','1','2024-05-21','220094','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6978332884','2024-07-09','2024-05-31','Sometimes','Usually','8','Definitely no','NULL','NULL','1','3','2024-05-31','220095','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6978336149','2024-07-02','2024-05-21','Usually','Always','10-Best possible','Definitely yes','Some college','English','1','6','2024-05-21','220096','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6978337433','2024-07-16','2024-06-01','Never','Always','6','Probably yes','4-yr coll. grad.','English','1','1','2024-06-01','220097','Removal')
INSERT INTO dbo.f_XML_Data VALUES ('6978344956','2024-07-09','2024-06-01','Always','Usually','8','Probably yes','Some college','NULL','1','3','2024-06-01','220098','Storage')
INSERT INTO dbo.f_XML_Data VALUES ('6978347260','2024-07-12','2024-05-23','Usually','Usually','10-Best possible','Definitely yes','Some college','English','1','6','2024-05-23','220099','Storage')

当前问题

原SQL代码会生成多个Header标签，每个Header对应一个coredata，不符合“单个Header包含所有coredata”的要求。原代码如下：

SELECT 
    (SELECT 
         'Forward Design' AS [company-name],
         '782837' AS [client-id],
         main.[SurveyID] AS [SurveyID],
         DATEPART(YEAR, [ITDISDAT]) AS [rsp-yr],
         DATEPART(MONTH, [ITDISDAT]) AS [rsp-month]
     FROM 
         dbo.f_XML_Data hdr
     WHERE 
         hdr.[SurveyID] = main.[SurveyID]
     FOR XML PATH('Header'), TYPE),

    (SELECT 
         (SELECT 
              [ITHCAHPS] AS [surveysub-id],
              DATEPART(YEAR, [ITDISDAT]) AS [rsp-yr],
              DATEPART(MONTH, [ITDISDAT]) AS [rsp-month],
              [ITADMSRC] AS [source],  
              [ITSERVIC] AS [principal-reason],
              [ITDCGSTA] AS [status],  -- Already translated for PG in the file we send
              [CMS_30] AS [language],
              DATEDIFF(DAY, CAST([DisDate] AS DATE), CAST([RecDate] AS DATE)) AS [lag-time],
              '52' AS [supplemental-question-count]
          FROM 
              dbo.f_XML_Data admin
          WHERE 
              admin.[SurveyID] = main.[SurveyID]
          FOR XML PATH(''), ROOT('administration'), TYPE),
         (SELECT
              [CMS_10] AS [cleanliness],
              [CMS_11] AS [quiet],
              [CMS_23] AS [overall-rate],
              [CMS_24] AS [recommend],
              [CMS_27] AS [education],
              [CMS_30] AS [language-speak]
          FROM 
              dbo.f_XML_Data admin
          WHERE 
              admin.[SurveyID] = main.[SurveyID]
          FOR XML PATH(''), ROOT('response'), TYPE)
      FOR XML PATH(''), ROOT('coredata'), TYPE)
FROM 
    dbo.f_XML_Data main
FOR XML PATH(''), ROOT('monthlydata'), TYPE

修正后的SQL代码

调整嵌套逻辑，将所有coredata的生成查询作为Header标签的子节点，确保仅生成一个Header并包含全部coredata：

SELECT 
    'Forward Design' AS [company-name],
    '782837' AS [client-id],
    -- 取第一条数据的Header字段（若需固定值可直接替换为常量）
    (SELECT TOP 1 [SurveyID] FROM dbo.f_XML_Data) AS [SurveyID],
    (SELECT TOP 1 DATEPART(YEAR, [ITDISDAT]) FROM dbo.f_XML_Data) AS [rsp-yr],
    (SELECT TOP 1 DATEPART(MONTH, [ITDISDAT]) FROM dbo.f_XML_Data) AS [rsp-month],
    -- 嵌套生成所有coredata节点
    (SELECT
         (SELECT 
              [ITHCAHPS] AS [surveysub-id],
              DATEPART(YEAR, [ITDISDAT]) AS [rsp-yr],
              DATEPART(MONTH, [ITDISDAT]) AS [rsp-month],
              [ITADMSRC] AS [source],  
              [ITSERVIC] AS [principal-reason],
              [ITDCGSTA] AS [status],
              [CMS_30] AS [language],
              DATEDIFF(DAY, CAST([DisDate] AS DATE), CAST([RecDate] AS DATE)) AS [lag-time],
              '52' AS [supplemental-question-count]
          FOR XML PATH(''), ROOT('administration'), TYPE),
         (SELECT
              [CMS_10] AS [cleanliness],
              [CMS_11] AS [quiet],
              [CMS_23] AS [overall-rate],
              [CMS_24] AS [recommend],
              [CMS_27] AS [education],
              [CMS_30] AS [language-speak]
          FOR XML PATH(''), ROOT('response'), TYPE)
     FROM dbo.f_XML_Data
     FOR XML PATH('coredata'), TYPE)
FOR XML PATH('Header'), ROOT('monthlydata'), TYPE

逻辑说明

外层直接生成Header标签，包含固定的company-name、client-id，以及从表中提取的SurveyID、rsp-yr、rsp-month
将所有coredata的生成逻辑嵌套在Header内部，通过FOR XML PATH('coredata')生成多个coredata节点，每个节点包含对应的administration和response子节点
最终通过ROOT('monthlydata')生成根标签，完全匹配需求结构

期望生成的XML结构（示例）

<monthlydata>
  <Header>
    <company-name>Forward Design</company-name>
    <client-id>782837</client-id>
    <SurveyID>6561942362</SurveyID>
    <rsp-yr>2024</rsp-yr>
    <rsp-month>3</rsp-month>
  <coredata>
    <administration>
      <surveysub-id>220090</surveysub-id>
      <rsp-yr>2024</rsp-yr>
      <rsp-month>3</rsp-month>
      <source>1</source>
      <principal-reason>Storage</principal-reason>
      <status>6</status>
      <language>English</language>
      <lag-time>120</lag-time>
      <supplemental-question-count>52</supplemental-question-count>
    </administration>
    <response>
      <cleanliness>Always</cleanliness>
      <quiet>Always</quiet>
      <overall-rate>8</overall-rate>
      <recommend>Definitely yes</recommend>
      <education>Some college</education>
      <language-speak>English</language-speak>
    </response>
  </coredata>
  <coredata>
    <administration>
      <surveysub-id>220091</surveysub-id>
      <rsp-yr>2024</rsp-yr>
      <rsp-month>4</rsp-month>
      <source>1</source>
      <principal-reason>Storage</principal-reason>
      <status>3</status>
      <language>English</language>
      <lag-time>96</lag-time>
      <supplemental-question-count>52</supplemental-question-count>
    </administration>
    <response>
      <cleanliness>Always</cleanliness>
      <quiet>Always</quiet>
      <overall-rate>9</overall-rate>
      <recommend>Definitely yes</recommend>
      <education>Some college</education>
      <language-speak>English</language-speak>
    </response>
  </coredata>
  </Header>
</monthlydata>

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

SQL Server 2016生成嵌套XML：消除重复Header标签的实现需求

环境信息 #

需求说明 #

表结构 #

测试数据 #

当前问题 #

修正后的SQL代码 #

逻辑说明 #

期望生成的XML结构（示例） #