使用T-SQL生成XML时消除重复元素及格式调整问题

针对你遇到的三个问题，直接给落地方案： #

1. 同一客户只输出一次Metadata、notification，仅address重复 #

要避免父元素重复，必须用嵌套FOR XML结构——外层处理客户级别的固定元素，内层单独循环生成address。

举个实际的写法（假设你有客户主表Customers和地址表CustomerAddresses）：

SELECT
    -- 客户级别的Metadata，只输出一次
    (SELECT 
         '2024-05-20' AS [CreationDate],
         'SYSTEM' AS [CreatedBy]
     FOR XML PATH('Metadata'), TYPE),
    -- 客户级别的Notification，只输出一次
    (SELECT 
         'alert@example.com' AS [Email],
         '1234567890' AS [Phone]
     FOR XML PATH('Notification'), TYPE),
    -- 嵌套处理natural_person和address集合
    (SELECT
         c.CustomerID AS [ID],
         c.FullName AS [Name],
         -- 内层循环生成多个address元素
         (SELECT
              ca.Street AS [Street],
              ca.City AS [City],
              ca.PostalCode AS [PostalCode]
          FROM CustomerAddresses ca
          WHERE ca.CustomerID = c.CustomerID
          FOR XML PATH('address'), TYPE)
     FROM Customers c
     WHERE c.CustomerID = @CustomerID -- 按需筛选客户
     FOR XML PATH('natural_person'), TYPE, ELEMENTS XSINIL)
FOR XML PATH(''), ROOT('CustomerData'), TYPE

这里外层用FOR XML PATH('')去掉默认的行标签，内层address用FOR XML PATH('address')直接生成指定标签，保证同一客户下只有一套Metadata、Notification，address可以重复。

2. 彻底移除标签 #

默认FOR XML PATH会自动加<row>，解决起来很简单：

• 顶层查询用FOR XML PATH('')，不要指定默认的PATH参数（默认值是row）
• 嵌套查询里，要生成自定义标签的话，直接写FOR XML PATH('你的标签名')，比如上面的FOR XML PATH('address')，就不会出现row标签

3. 无客户详情时输出<natural_person xsi:nil="true"/> #

要实现空值时输出nil属性，必须用ELEMENTS XSINIL参数，同时判断是否有客户数据：

SELECT
    (SELECT 
         GETDATE() AS [CreationDate],
         'APP_SYSTEM' AS [CreatedBy]
     FOR XML PATH('Metadata'), TYPE),
    (SELECT 
         'support@thirdparty.com' AS [Email],
         '9876543210' AS [Phone]
     FOR XML PATH('Notification'), TYPE),
    -- 判断客户是否存在，不存在就输出nil的natural_person
    CASE WHEN EXISTS(SELECT 1 FROM Customers c WHERE c.CustomerID = @TargetID)
         THEN (SELECT
                  c.CustomerID AS [ID],
                  c.FullName AS [Name],
                  (SELECT
                       ca.Street AS [Street],
                       ca.City AS [City],
                       ca.PostalCode AS [PostalCode]
                   FROM CustomerAddresses ca
                   WHERE ca.CustomerID = c.CustomerID
                   FOR XML PATH('address'), TYPE)
               FROM Customers c
               WHERE c.CustomerID = @TargetID
               FOR XML PATH('natural_person'), TYPE)
         ELSE (SELECT NULL AS [natural_person] FOR XML PATH(''), TYPE, ELEMENTS XSINIL)
    END
FOR XML PATH(''), ROOT('CustomerRecord'), TYPE

这样当查询不到客户数据时，就会生成<natural_person xsi:nil="true"/>，而不是一堆空的子元素。

结合示例数据的完整测试 #

假设你有如下测试数据：

-- 测试客户表
DECLARE @Customers TABLE (CustomerID INT, FullName VARCHAR(50))
INSERT INTO @Customers VALUES (1, 'John Doe'), (2, NULL)

-- 测试地址表
DECLARE @CustomerAddresses TABLE (CustomerID INT, Street VARCHAR(100), City VARCHAR(50), PostalCode VARCHAR(20))
INSERT INTO @CustomerAddresses VALUES 
(1, '123 Main St', 'New York', '10001'),
(1, '456 Oak Ave', 'Brooklyn', '11201')

把上面的查询里的@TargetID设为1，输出会包含一套Metadata、Notification，两个address元素，完全没有row标签；设为3（不存在的客户），就会输出nil的natural_person。

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