SQL - XML | How to create sub tags on XML document using FOR XML

Question

I want to reproduce this output using FOR 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>

I got this far with my query:

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

and got this output:

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

My tables are:

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

And version is: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

The idea, as you can see in the first example is to 'isolate' the diferents tables (Materials, Equipment, Categories) and have all the records of the table Recipe as part of the Gear tag. Can someone help me figure what im doing wrong?

@EDIT:

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>

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.

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

While asking a question you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements. (2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL. (3) Desired output based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jun 21, 2022 at 19:24
Edited for the info @YitzhakKhabinsky asked for. Thanks for replying. — Leandro Dias
– Leandro Dias, Commented Jun 21, 2022 at 20:24
@DaleK i usually do, left as original to try a more direct approach in the answers. Thx tho. — Leandro Dias
– Leandro Dias, Commented Jun 21, 2022 at 20:25
Well it makes it much harder for us to read your query... hence my recommendation. — Dale K
– Dale K, Commented Jun 21, 2022 at 20:25

Yitzhak Khabinsky · Accepted Answer · 2022-06-21 21:40:05Z

2

Please try the following solution.

I am not sure about the relationships between tables.

You may need to adjust the WHERE clauses...

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;

Output

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

edited Jun 21, 2022 at 21:40

answered Jun 21, 2022 at 21:33

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Leandro Dias Over a year ago

Very thanks, its exactly what i needed. Gotta change some where, as you said, but its exactly what i needed.

Leandro Dias Over a year ago

Yitzhak, would you mind helping me with a more scalable solution? If you look at my current database, you will find others Recipes attached to other Equipments. I will edit the thread to do an example. But the idea behind it is to every entry to Recipe (whose is attached to a Equipment and the equipment is attached to a Category) returns as an instance of Gear.,

Yitzhak Khabinsky Over a year ago

Please create a brand new question, and use DDL and sample data population section like in my answer

Leandro Dias Over a year ago

Sure. Here it is stackoverflow.com/questions/72721676/…

Collectives™ on Stack Overflow

SQL - XML | How to create sub tags on XML document using FOR XML

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related