SQL -XML |如何在XML文档上使用XML创建子标签

Question

我想使用XML来重现此输出：

<Gear id='1'>
  <materials>
    <material>
      <id>1</id>
      <material_name> Leather </material_name>
    </material>
    <material>
      <id>2</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> 

我的查询很远：得到

select  mat.id, mat.material_name , c.id, c.name, e.equipment_name, e.category_id,  r.equipment_id, r.material_id, r.material_quantity
from Recipe as r   
inner join Equipment as e on r.equipment_id = e.id
inner join Categories as c on c.id = e.category_id
inner join Materials as mat on r.material_id = mat.id
for xml Auto,  Root('Gear'), Elements

了此输出：

<Gear>
  <Materials>
    <id>1</id>
    <material_name>Bar</material_name>
    <Categories>
      <id>1</id>
      <name>Warrior Weapons</name>
      <Equipment>
        <equipment_name>Claymore</equipment_name>
        <category_id>1</category_id>
        <Recipe>
          <equipment_id>1</equipment_id>
          <material_id>1</material_id>
          <material_quantity>20</material_quantity>
        </Recipe>
      </Equipment>
    </Categories>
  </Materials>
  <Materials>
    <id>2</id>
    <material_name>Leather</material_name>
    <Categories>
      <id>1</id>
      <name>Warrior Weapons</name>
      <Equipment>
        <equipment_name>Claymore</equipment_name>
        <category_id>1</category_id>
        <Recipe>
          <equipment_id>1</equipment_id>
          <material_id>2</material_id>
          <material_quantity>12</material_quantity>
        </Recipe>
      </Equipment>
    </Categories>
  </Materials>
  <Materials>

我的桌子是：

USE [master]
GO
/****** Object:  Database [CrafterAlbion]    Script Date: 21/06/2022 18:05:10 ******/
CREATE DATABASE [CrafterAlbion]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'CrafterAlbion', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\CrafterAlbion.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'CrafterAlbion_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\CrafterAlbion_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [CrafterAlbion] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [CrafterAlbion].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [CrafterAlbion] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [CrafterAlbion] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [CrafterAlbion] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [CrafterAlbion] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [CrafterAlbion] SET ARITHABORT OFF 
GO
ALTER DATABASE [CrafterAlbion] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [CrafterAlbion] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [CrafterAlbion] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [CrafterAlbion] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [CrafterAlbion] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [CrafterAlbion] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [CrafterAlbion] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [CrafterAlbion] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [CrafterAlbion] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [CrafterAlbion] SET  DISABLE_BROKER 
GO
ALTER DATABASE [CrafterAlbion] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [CrafterAlbion] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [CrafterAlbion] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [CrafterAlbion] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [CrafterAlbion] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [CrafterAlbion] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [CrafterAlbion] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [CrafterAlbion] SET RECOVERY FULL 
GO
ALTER DATABASE [CrafterAlbion] SET  MULTI_USER 
GO
ALTER DATABASE [CrafterAlbion] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [CrafterAlbion] SET DB_CHAINING OFF 
GO
ALTER DATABASE [CrafterAlbion] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [CrafterAlbion] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [CrafterAlbion] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [CrafterAlbion] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'CrafterAlbion', N'ON'
GO
ALTER DATABASE [CrafterAlbion] SET QUERY_STORE = OFF
GO
USE [CrafterAlbion]
GO
/****** Object:  Table [dbo].[Categories]    Script Date: 21/06/2022 18:05:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Categories](
    [id] [int] NULL,
    [name] [nvarchar](150) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Equipment]    Script Date: 21/06/2022 18:05:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Equipment](
    [id] [int] NULL,
    [equipment_name] [nvarchar](50) NULL,
    [category_id] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Materials]    Script Date: 21/06/2022 18:05:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Materials](
    [id] [int] NULL,
    [material_name] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Recipe]    Script Date: 21/06/2022 18:05:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Recipe](
    [equipment_id] [int] NULL,
    [material_id] [int] NULL,
    [material_quantity] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Categories] ([id], [name]) VALUES (NULL, NULL)
GO
INSERT [dbo].[Categories] ([id], [name]) VALUES (NULL, NULL)
GO
INSERT [dbo].[Categories] ([id], [name]) VALUES (1, N'Warrior Weapons')
GO
INSERT [dbo].[Categories] ([id], [name]) VALUES (2, N'Hunter Weapons')
GO
INSERT [dbo].[Categories] ([id], [name]) VALUES (3, N'Mage Weapons')
GO
INSERT [dbo].[Categories] ([id], [name]) VALUES (NULL, NULL)
GO
INSERT [dbo].[Categories] ([id], [name]) VALUES (4, N'Warrior Armours')
GO
INSERT [dbo].[Equipment] ([id], [equipment_name], [category_id]) VALUES (1, N'Claymore', 1)
GO
INSERT [dbo].[Equipment] ([id], [equipment_name], [category_id]) VALUES (2, N'Bow', 2)
GO
INSERT [dbo].[Equipment] ([id], [equipment_name], [category_id]) VALUES (3, N'Fire Staff', 3)
GO
INSERT [dbo].[Equipment] ([id], [equipment_name], [category_id]) VALUES (4, N'Knight Armor', 4)
GO
INSERT [dbo].[Materials] ([id], [material_name]) VALUES (1, N'Bar')
GO
INSERT [dbo].[Materials] ([id], [material_name]) VALUES (2, N'Leather')
GO
INSERT [dbo].[Materials] ([id], [material_name]) VALUES (3, N'Plank')
GO
INSERT [dbo].[Materials] ([id], [material_name]) VALUES (4, N'Cloth')
GO
INSERT [dbo].[Recipe] ([equipment_id], [material_id], [material_quantity]) VALUES (1, 1, 20)
GO
INSERT [dbo].[Recipe] ([equipment_id], [material_id], [material_quantity]) VALUES (1, 2, 12)
GO
INSERT [dbo].[Recipe] ([equipment_id], [material_id], [material_quantity]) VALUES (2, 3, 32)
GO
INSERT [dbo].[Recipe] ([equipment_id], [material_id], [material_quantity]) VALUES (3, 3, 16)
GO
INSERT [dbo].[Recipe] ([equipment_id], [material_id], [material_quantity]) VALUES (3, 1, 8)
GO
INSERT [dbo].[Recipe] ([equipment_id], [material_id], [material_quantity]) VALUES (4, 1, 16)
GO
USE [master]
GO
ALTER DATABASE [CrafterAlbion] SET  READ_WRITE 
GO

和版本是：Microsoft SQL Server 2019（RTM）-15.0.2000.5（x64），

当您的想法时，可以在第一个示例中看到的是“隔离”差异表（材料，设备，类别），并拥有表的所有记录食谱作为 Gear> Gear 标签的一部分。 有人可以帮我找我做错了什么吗？

@Edit：

这是包含Claymore，弓和骑士装甲的实例 Gear 的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>

因此，如您所见，它就像一个名为 Gear ，包含材料的数组，类别对象，设备对象和配方的阵列。

如果添加了监护装甲的新食谱，我希望查询将其重现为具有上述结构的齿轮实例。

齿轮ID只是一个参考，因此我认为将附加到真实属性（例如category_id）并不重要。

Answer 1

请尝试以下解决方案。

我不确定表之间的关系。

您可能需要调整 条款...

sql

-- 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

DECLARE @categoryID INT = 1;

SELECT @categoryID AS [@id]
, (
    SELECT * FROM dbo.Materials
    WHERE id <= 2
    FOR XML PATH('material'), TYPE, ROOT('materials')
)
, (
    SELECT * FROM dbo.Categories
    WHERE id = @categoryID
    FOR XML PATH('category'), TYPE
)
, (
    SELECT id, equipment_name, category_id AS category
    FROM dbo.Equipment
    WHERE category_id = @categoryID
    FOR XML PATH('equipment'), TYPE
)
, (
    SELECT *
    FROM dbo.Recipe
    WHERE equipment_id = 1 AND material_id <= 2
    FOR XML PATH('recipe'), TYPE, ROOT('recipes')
)
FOR XML PATH('Gear'), TYPE;

输出

<Gear id="1">
  <materials>
    <material>
      <id>1</id>
      <material_name>Bar</material_name>
    </material>
    <material>
      <id>2</id>
      <material_name>Leather</material_name>
    </material>
  </materials>
  <category>
    <id>1</id>
    <name>Warrior Weapons</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>20</material_quantity>
    </recipe>
    <recipe>
      <equipment_id>1</equipment_id>
      <material_id>2</material_id>
      <material_quantity>12</material_quantity>
    </recipe>
  </recipes>
</Gear>