Home > Software design >  SQL - XML | How to create sub tags on XML document using FOR XML
SQL - XML | How to create sub tags on XML document using FOR XML

Time：06-22

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?

CodePudding user response：

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>

Page link：https//www.codepudding.com/Softwaredesign/449949.html

Prev:Create multiple select dropdown on server side
Next:Select Best Indexes For Run a Query
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding