Csharp 使用 SQL Server FOR XML 创建可扩展的 XML 文档

我想使用FOR XML将此输出复制为从数据库到XML的可伸缩查询

<root>
<Gear id='1'>
  <materials>
    <material>
      <id>2</id>
      <material_name> Leather </material_name>
    </material>
    <material>
      <id>1</id>
      <material_name> Bar</material_name>
    </material>
  </materials>

  <category>
    <id>1</id>
    <name>Warriors Weapon</name>
  </category>

  <equipment>
    <id> 1</id>
    <equipment_name> Claymore </equipment_name>
    <category> 1</category>
  </equipment>

  <recipes>
    <recipe>
      <equipment_id>1</equipment_id>
      <material_id>1</material_id>
      <material_quantity>12</material_quantity>
    </recipe>
    <recipe>
      <equipment_id> 1 </equipment_id>
      <material_id>2</material_id>
      <material_quantity>20</material_quantity>
    </recipe>
  </recipes>
</Gear>
<Gear id='2'>
    <materials>
      <material>
        <id>3</id>
        <material_name> Plank </material_name>
      </material>
    </materials>

    <category>
      <id>2</id>
      <name>Hunter Weapons</name>
    </category>

    <equipment>
      <id> 2</id>
      <equipment_name> Bow </equipment_name>
      <category> 2</category>
    </equipment>

    <recipes>
      <recipe>
        <equipment_id> 2 </equipment_id>
        <material_id>3</material_id>
        <material_quantity>36</material_quantity>
      </recipe>
    </recipes>
  </Gear>
<Gear id='3'>
    <materials>
      <material>
        <id>1</id>
        <material_name> Bar </material_name>
      </material>
    </materials>

    <category>
      <id>4</id>
      <name>Warrior Armour</name>
    </category>

    <equipment>
      <id> 4</id>
      <equipment_name> Knight Amor </equipment_name>
      <category> 4</category>
    </equipment>

    <recipes>
      <recipe>
        <equipment_id> 4 </equipment_id>
        <material_id>1</material_id>
        <material_quantity>16</material_quantity>
      </recipe>
    </recipes>
  </Gear>
</root>

这是我的实际数据库:

-- DDL and sample data population, start
USE tempdb;
GO

DROP TABLE IF EXISTS [dbo].[Categories];
DROP TABLE IF EXISTS [dbo].[Equipment];
DROP TABLE IF EXISTS [dbo].[Materials];
DROP TABLE IF EXISTS [dbo].[Recipe];

CREATE TABLE [dbo].[Categories](
    [id] [int] NULL,
    [name] [nvarchar](150) NULL
);
CREATE TABLE [dbo].[Equipment](
    [id] [int] NULL,
    [equipment_name] [nvarchar](50) NULL,
    [category_id] [int] NULL
);
CREATE TABLE [dbo].[Materials](
    [id] [int] NULL,
    [material_name] [nvarchar](50) NULL
);
CREATE TABLE [dbo].[Recipe](
    [equipment_id] [int] NULL,
    [material_id] [int] NULL,
    [material_quantity] [int] NULL
);

INSERT [dbo].[Categories] ([id], [name]) VALUES 
(1, N'Warrior Weapons'),
(2, N'Hunter Weapons'),
(3, N'Mage Weapons'),
(4, N'Warrior Armours');

INSERT [dbo].[Equipment] ([id], [equipment_name], [category_id]) VALUES 
(1, N'Claymore', 1),
(2, N'Bow', 2),
(3, N'Fire Staff', 3),
(4, N'Knight Armor', 4);

INSERT [dbo].[Materials] ([id], [material_name]) VALUES 
(1, N'Bar'),
(2, N'Leather'),
(3, N'Plank'),
(4, N'Cloth');

INSERT [dbo].[Recipe] ([equipment_id], [material_id], [material_quantity]) VALUES 
(1, 1, 20),
(1, 2, 12),
(2, 3, 32),
(3, 3, 16),
(3, 1, 8),
(4, 1, 16);
-- DDL and sample data population, end

因此，正如您所看到的，它就像一个名为Gear的类，包含一个material 数组、一个类别对象、一个设备对象和一个配方array.

如果添加了守护者护甲的新配方，我希望查询将其复制为具有上述 struct 的装备实例.

这样地:

<root>
[...]
<Gear id='4'>
  <materials>
      <material>
        <id>1</id>
        <material_name> Bar </material_name>
      </material>
    </materials>

    <category>
      <id>4</id>
      <name>Warrior Armour</name>
    </category>

    <equipment>
      <id> 5</id>
      <equipment_name> Guardian Amor </equipment_name>
      <category> 4</category>
    </equipment>

    <recipes>
      <recipe>
        <equipment_id> 5 </equipment_id>
        <material_id>1</material_id>
        <material_quantity>16</material_quantity>
      </recipe>
    </recipes>
  </Gear>
</root>

gear ID只是一个参考，所以我认为附加到真实属性(如category\u ID)并不重要.