How to generate XML File from table

Abdullah S Abdelhay

I have a table in database :

CREATE TABLE [LeftMenu] (
[NodeID] [int] NOT NULL ,
[ParentNodeID] [int] NULL ,
[FK_LangID] [int] NULL ,
[Text] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastlevel] [bit] NULL ,
[linkLevel] [bit] NULL ,
CONSTRAINT [PK_LeftMenu] PRIMARY KEY CLUSTERED
(
[NodeID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

and i want to generate XML file from this table with relation between 'NodeID' column and 'ParentNodeID' to fill ASP.net 2 Menu Control or TreeView Control like this example:

plz reply me. Thank you Abdullah Abdelhay

tptshepo

HI Run this in a console app and change the connection string!

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace MenuGen
{
class Program
{
public static string Crlf = System.Environment.NewLine;

    private class LeftMenuNode
    {
        public int NodeID = 0;
        public string ParentNodeID = string.Empty;
        public int FK\_LangID = 0;
        public string Text = string.Empty;
        public bool LastLevel = false;
        public bool LinkLevel = false;

        public List SubMenus;

        public LeftMenuNode()
        {
            SubMenus = new List();
        }
    }

    //get the menu data from the database
    static void GetDBMenus(ref DataSet ds)
    {
        string sql = "SELECT \* FROM LeftMenu";
        SqlCommand cmd = new SqlCommand(sql, new SqlConnection(@"data source=\*\*\*;initial catalog=\*\*\*;Integrated Security=True"));
        cmd.CommandType = CommandType.Text;
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        ds.Clear();
        using (adapter)
        {
            adapter.Fill(ds);
        }
    }

    static void Main(string\[\] args)
    {
        DataSet dsMenus = new DataSet("Menu");
        //get the list of menus
        GetDBMenus(ref dsMenus);

        //make sure we have a table to work with
        if (dsMenus.Tables.Count == 0)
            return;

        //kick off the menu gen process
        BuilMenuXML(ref dsMenus);

        //now that we've buld a class structure we can work with
        //all we have to do now if
    }

    static void BuilMenuXML(ref DataSet ds)
    {
        int tab = 0;
        string xml = string.Empty;

        xml = "

"
+ Crlf;

        //populate our custom menu class
        List menus = new List();
        foreach (DataRowView row in ds.Tables\[0\].DefaultView)
        {
            LeftMenuNode menuItem = new LeftMenuNode();
            menuItem.NodeID = Convert.ToInt32(row\["NodeID"\]);
            menuItem.ParentNodeID = Convert.ToString(row\["ParentNodeID"\] == DBNull.Value ? "" : row\["ParentNodeID"\]);
            menuItem.Text = Convert.ToString(row\["Text"\]);