我想將此輸出重現為使用 FOR XML 從資料庫到 XML 的可擴展查詢 這是包含 Claymore、弓和騎士盔甲實體齒輪的 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 的類，包含一個材料陣列、一個類別物件、一個設備物件和一個配方陣列。
如果添加了守護者盔甲的新配方，我希望查詢將其復制為具有上述結構的 Gear 實體。
像這樣：
<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>
齒輪 ID 只是一個參考，所以我認為附加到真實屬性（如 category_id）并不重要。
uj5u.com熱心網友回復：
請嘗試以下解決方案。
通過WHERE ...子句創建與父級的嵌套 XML 片段關系。
dbo.Equipment表是頂級父表。它用于組成<Gear id="N">XML 元素。
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 (
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)
FOR XML PATH(''), TYPE, ROOT('root');
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/494802.html