SQL Server FOR XML：生成带多子节点及属性的XML解决方案

需求：从SQL Server的TabKmenZbozi表生成指定结构的XML输出，要求包含带Deleted属性的group、goodsCard节点及对应子节点，同时为data节点添加type、destination、version属性。已编写SQL实现goodsCard节点的生成，但不清楚如何添加group节点以及为data节点设置属性，最终结合参考方案完成了需求。

表结构 #

CREATE TABLE [dbo].[TabKmenZbozi](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Nazev1] [nvarchar](50) NULL,
    [GroupGoodsID] [int] NULL,
    [GroupGoodsName] [nvarchar](50) NULL
) ON [PRIMARY]

测试数据 #

INSERT INTO TabKmenZbozi (Nazev1, GroupGoodsID, GroupGoodsName) 
VALUES
    ('2117 OF SWEDEN 7709901', 1, 'Meals'),
    ('BLIZZARD DOUBLE', 2, 'Drinks'),
    ('Test Test 1111', 1, 'Meals')

期望XML输出 #

<data type="TEST" destination="FILE" version="1">
  <content>
    <group Deleted="0">
        <GroupGoods ids="1" name="Meals"/>
        <GroupGoods ids="2" name="Drinks"/>
    </group>
      <goodsCard Deleted="2">
        <item ids="2217" idsGroup="2" plu="43"/>
        <item ids="2218" idsGroup="1" plu="43"/>
        <item ids="2219" idsGroup="1" plu="43"/>
        <item ids="2220" idsGroup="1" plu="43"/>
      </goodsCard>
  </content>
</data>

尝试的SQL语句 #

该语句可正确生成goodsCard节点，但缺少group节点及data节点属性。

SELECT 
  (SELECT 
    (SELECT 
       (SELECT TOP 10  
                 G.ID AS '@ids',
                 G.Nazev1 AS '@name'
        FROM TabKmenZbozi G
      FOR XML PATH('item'), TYPE)
    FOR XML PATH('goodsCard'), TYPE)
  FOR XML PATH ('content'), TYPE)
FOR XML PATH ('data'), TYPE;

最终解决方案SQL #

SELECT 
  'TEST' AS '@type',
  'FILE' AS '@destination',
  '1' AS '@version',
   (
    SELECT
      (
       SELECT
         '0' AS '@DELETED',
         (
         SELECT
           TKZ.ID AS 'GroupGoods/@ids',
           TKZ.Nazev1 AS 'GroupGoods/@nazev'
         FROM
           dbo.TabKmenZbozi AS TKZ
         FOR XML PATH(''), TYPE
         )
       FOR XML PATH('group'), TYPE
      ),
      (
       SELECT
         '2' AS '@DELETED',
         (
         SELECT TOP 10
           TKX.ID AS 'goodsCard/@ids',
           TKX.Nazev1 AS 'goodsCard/@nazev'
         FROM
           dbo.TabKmenZbozi AS TKX
           FOR XML PATH(''), TYPE
         )
       FOR XML PATH('goodsCard'), TYPE
      )
    FOR XML PATH('content'), TYPE
   )
FOR XML PATH('data'), TYPE;

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