hello
i have below 4 tables and i want to generate the xml output like below sample in c# or in sql with for xml caluse.
please advice
CREATE TABLE [dbo].[Ingredients](
[IngredientsId] [int] IDENTITY(1,1) NOT NULL,
[Recipeid] [int] NULL,
[Description] [nvarchar](50) NULL,
CONSTRAINT [PK_Ingredients] PRIMARY KEY CLUSTERED
(
[IngredientsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Nutritionfacts] Script Date: 10/22/2019 12:38:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Nutritionfacts](
[NutritionFactsId] [int] IDENTITY(1,1) NOT NULL,
[RecipeId] [int] NULL,
[Description] [nvarchar](50) NULL,
CONSTRAINT [PK_Nutritionfacts] PRIMARY KEY CLUSTERED
(
[NutritionFactsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Recipe] Script Date: 10/22/2019 12:38:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Recipe](
[RecipeId] [int] IDENTITY(1,1) NOT NULL,
[Recipename] [nvarchar](50) NULL,
[Description] [nvarchar](50) NULL,
CONSTRAINT [PK_Recipe] PRIMARY KEY CLUSTERED
(
[RecipeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Ingredients] ON
GO
INSERT [dbo].[Ingredients] ([IngredientsId], [Recipeid], [Description]) VALUES (1, 1, N'Ing one')
GO
INSERT [dbo].[Ingredients] ([IngredientsId], [Recipeid], [Description]) VALUES (2, 1, N'Ing Two')
GO
INSERT [dbo].[Ingredients] ([IngredientsId], [Recipeid], [Description]) VALUES (3, 1, N'Ing Three')
GO
INSERT [dbo].[Ingredients] ([IngredientsId], [Recipeid], [Description]) VALUES (4, 2, N'Ing one')
GO
INSERT [dbo].[Ingredients] ([IngredientsId], [Recipeid], [Description]) VALUES (5, 2, N'Ing two')
GO
SET IDENTITY_INSERT [dbo].[Ingredients] OFF
GO
SET IDENTITY_INSERT [dbo].[Nutritionfacts] ON
GO
INSERT [dbo].[Nutritionfacts] ([NutritionFactsId], [RecipeId], [Description]) VALUES (1, 1, N'Fact one')
GO
INSERT [dbo].[Nutritionfacts] ([NutritionFactsId], [RecipeId], [Description]) VALUES (2, 1, N'Fact two')
GO
INSERT [dbo].[Nutritionfacts] ([NutritionFactsId], [RecipeId], [Description]) VALUES (3, 2, N'Fact one')
GO
INSERT [dbo].[Nutritionfacts] ([NutritionFactsId], [RecipeId], [Description]) VALUES (4, 2, N'Fact Two')
GO
SET IDENTITY_INSERT [dbo].[Nutritionfacts] OFF
GO
SET IDENTITY_INSERT [dbo].[Recipe] ON
GO
INSERT [dbo].[Recipe] ([RecipeId], [Recipename], [Description]) VALUES (1, N'Recipe One', N'First Recipe')
GO
INSERT [dbo].[Recipe] ([RecipeId], [Recipename], [Description]) VALUES (2, N' Recipe Two', N'Second Recipe')
GO
INSERT [dbo].[Recipe] ([RecipeId], [Recipename], [Description]) VALUES (3, NULL, N'')
GO
SET IDENTITY_INSERT [dbo].[Recipe] OFF
GO
xml output
<?xml version="1.0" encoding="UTF-8"?>
<!-- ENGLISH EXAMPLE -->
<document>
<recipe>
<RecipeName>Recipe one</RecipeName>
<RecipeID>1</RecipeID>
<Description>Des</Description>
<Ingredients>
<item>
<Description>Daisy Light Sour Cream</Description>
<IngredientsId>1</IngredientsId>
<Recipeid>1</Recipeid>
</item>
<!-- Repeat for all ingredients -->
</Ingredients>
<NutritionFacts>
<item>
<Description>desc</Description>
<Recipeid>1</Recipeid>
<NutritionFactsId>1</NutritionFactsId>
</item>
<!-- Repeat for all nutrition facts -->
</NutritionFacts>
</recipe>
</document>
