Creating a scalable XML document with SQL Server FOR XML

Question

I want to reproduce this output as an scalable query from the datebase to XML using FOR XML This is the example of a XML containing an instance Gear of a Claymore, a Bow and a Knight's Armor:

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

This is my actual database:

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

So, as you can see it would be like a Class named Gear, containing an array of Materials, a Category object, an Equipment object and an array of Recipe.

If a new recipe of a Guardian armor is added, i want the query to reproduce it as an instance of Gear with this structure above.

Like this:

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

The gear ID is just a reference, so i dont think its important to be attached to a real attribute (like category_id).

Answer 1

Please try the following solution.

Nested XML fragments relationships with the parent are created via WHERE ... clause.

The dbo.Equipment table is the top parent. It is used to compose the <Gear id="N"> XML elements.

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

SELECT id AS [@id]
, (
    SELECT m.* 
    FROM dbo.Materials AS m 
        INNER JOIN dbo.Recipe AS r ON m.id = r.material_id
    WHERE r.equipment_id = e.id
    FOR XML PATH('material'), TYPE, ROOT('materials')
)
, (
    SELECT * 
    FROM dbo.Categories AS c
    WHERE c.id = e.category_id
    FOR XML PATH('category'), TYPE
)
, (
    SELECT id, equipment_name, category_id AS category
    FROM dbo.Equipment AS c
    WHERE c.id = e.id
    FOR XML PATH('equipment'), TYPE
)
, (
    SELECT *
    FROM dbo.Recipe AS r
    WHERE r.equipment_id = e.id
    FOR XML PATH('recipe'), TYPE, ROOT('recipes')
)
FROM dbo.Equipment AS e
FOR XML PATH('Gear'), TYPE, ROOT('root');

db<>fiddle