Create (Generate) nested XML file from Database in ASP.Net using C# and VB.Net

nauna

SOLVED CLOSED ACTIVE ANSWERED
User: nauna
Posted: on Oct 22, 2019 01:51 AM
Forum: ASP.Net Web Forms
Answer: 2
Views: 8681
Sample Code: Download

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>

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Oct 22, 2019 07:34 AM
Report

Hi nauna,

Refer below links.

http://www.tomasvera.com/programming/generating-hierarchical-nested-xml-from-a-dataset/

https://stackoverflow.com/questions/20935102/how-to-create-nested-xml-from-datatable-or-dataset-without-looping

dharmend...

0 3

Reply
ANSWER
User: dharmendr
Replied: on Oct 22, 2019 07:45 AM
Modified: on Oct 22, 2019 07:47 AM
Report

Hi nauna,

Refer below code.

protected void Page_Load(object sender, EventArgs e)
{
    DataSet ds = GetData();
    DataRelation relation = new DataRelation("relation", ds.Tables["Recipe"].Columns["Recipeid"], ds.Tables["Ingredients"].Columns["Recipeid"]);
    DataRelation relation1 = new DataRelation("relation1", ds.Tables["Ingredients"].Columns["IngredientsId"], ds.Tables["Nutritionfacts"].Columns["Recipeid"]);
    relation.Nested = true;
    relation1.Nested = true;
    ds.Relations.Add(relation);
    ds.Relations.Add(relation1);

    ds.WriteXml(Server.MapPath("test.xml"));
}

private DataSet GetData()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT * FROM Recipe;SELECT * FROM Ingredients;SELECT * FROM Nutritionfacts";
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet("Document"))
            {
                sda.Fill(ds);
                ds.Tables[0].TableName = "Recipe";
                ds.Tables[1].TableName = "Ingredients";
                ds.Tables[2].TableName = "Nutritionfacts";
                return ds;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim ds As DataSet = GetData()
    Dim relation As DataRelation = New DataRelation("relation", ds.Tables("Recipe").Columns("Recipeid"), ds.Tables("Ingredients").Columns("Recipeid"))
    Dim relation1 As DataRelation = New DataRelation("relation1", ds.Tables("Ingredients").Columns("IngredientsId"), ds.Tables("Nutritionfacts").Columns("Recipeid"))
    relation.Nested = True
    relation1.Nested = True
    ds.Relations.Add(relation)
    ds.Relations.Add(relation1)
    ds.WriteXml(Server.MapPath("test.xml"))
End Sub

Private Function GetData() As DataSet
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT * FROM Recipe;SELECT * FROM Ingredients;SELECT * FROM Nutritionfacts"
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using ds As DataSet = New DataSet("Document")
                sda.Fill(ds)
                ds.Tables(0).TableName = "Recipe"
                ds.Tables(1).TableName = "Ingredients"
                ds.Tables(2).TableName = "Nutritionfacts"
                Return ds
            End Using
        End Using
    End Using
End Function

Namespaces

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Output

<?xml version="1.0" standalone="yes"?>
<Document>
  <Recipe>
    <RecipeId>1</RecipeId>
    <Recipename>Recipe One</Recipename>
    <Description>First Recipe</Description>
    <Ingredients>
      <IngredientsId>1</IngredientsId>
      <Recipeid>1</Recipeid>
      <Description>Ing one</Description>
      <Nutritionfacts>
        <NutritionFactsId>1</NutritionFactsId>
        <RecipeId>1</RecipeId>
        <Description>Fact one</Description>
      </Nutritionfacts>
      <Nutritionfacts>
        <NutritionFactsId>2</NutritionFactsId>
        <RecipeId>1</RecipeId>
        <Description>Fact two</Description>
      </Nutritionfacts>
    </Ingredients>
    <Ingredients>
      <IngredientsId>2</IngredientsId>
      <Recipeid>1</Recipeid>
      <Description>Ing Two</Description>
      <Nutritionfacts>
        <NutritionFactsId>3</NutritionFactsId>
        <RecipeId>2</RecipeId>
        <Description>Fact one</Description>
      </Nutritionfacts>
      <Nutritionfacts>
        <NutritionFactsId>4</NutritionFactsId>
        <RecipeId>2</RecipeId>
        <Description>Fact Two</Description>
      </Nutritionfacts>
    </Ingredients>
    <Ingredients>
      <IngredientsId>3</IngredientsId>
      <Recipeid>1</Recipeid>
      <Description>Ing Three</Description>
    </Ingredients>
  </Recipe>
  <Recipe>
    <RecipeId>2</RecipeId>
    <Recipename> Recipe Two</Recipename>
    <Description>Second  Recipe</Description>
    <Ingredients>
      <IngredientsId>4</IngredientsId>
      <Recipeid>2</Recipeid>
      <Description>Ing one</Description>
    </Ingredients>
    <Ingredients>
      <IngredientsId>5</IngredientsId>
      <Recipeid>2</Recipeid>
      <Description>Ing two</Description>
    </Ingredients>
  </Recipe>
  <Recipe>
    <RecipeId>3</RecipeId>
    <Description />
  </Recipe>
</Document>

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Create (Generate) nested XML file from Database in ASP.Net using C# and VB.Net

Company

Explore

Follow us

Contact Us