Below are my table structures; using those tables I want to create XML file.
CREATE TABLE [dbo].[Security_Module_Menu](
[Client_Company_ID] [smallint] NOT NULL,
[Module_ID] [tinyint] NOT NULL,Module_ID,Menu_ID,Reference_Menu_ID
[Menu_ID] [int] NOT NULL,
[Reference_Menu_ID] [int] NULL,
[Menu_Name] [nvarchar](50) NULL,
[Menu_Description] [nvarchar](500) NULL,
[Menu_Type] [tinyint] NULL,
[Tree_Level] [tinyint] NULL,
[Target_URL] [nvarchar](200) NULL,
[Image_URL] [nvarchar](200) NULL,
[Confidentiality_Level] [tinyint] NULL,
[Hotkey] [nvarchar](5) NULL,
[ToolTips] [nvarchar](50) NULL,
[Show_In_Toolbar] [bit] NULL,
[Authentication_Level] [tinyint] NULL,
[Has_Parameter] [bit] NULL,
[Has_Charge] [bit] NULL,
[Is_Active] [bit] NULL,
[Active_Date] [smalldatetime] NULL,
[Record_Status] [tinyint] NULL,
[Maker_ID] [smallint] NULL,
[Make_Date] [smalldatetime] NULL,
[Checker_ID] [smallint] NULL,
[Check_Date] [smalldatetime] NULL,
[Authorizer_ID] [smallint] NULL,
[Authorize_Date] [smalldatetime] NULL,
[Record_Action_Type] [tinyint] NULL,
[Priority] [tinyint] NULL,
CONSTRAINT [PK_Security_Module_Menu] PRIMARY KEY CLUSTERED
([Client_Company_ID] ASC, [Module_ID] ASC, [Menu_ID] ASC)
)
GO
INSERT [dbo].[Security_Module_Menu] ([Client_Company_ID], [Module_ID], [Menu_ID], [Reference_Menu_ID], [Menu_Name], [Menu_Description], [Menu_Type], [Tree_Level], [Target_URL], [Image_URL], [Confidentiality_Level], [Hotkey], [ToolTips], [Show_In_Toolbar], [Authentication_Level], [Has_Parameter], [Has_Charge], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type], [Priority]) VALUES (1, 1, 1, 0, N'Forms', N'', 0, 1, N'', N'', 0, N'', N'', 0, 0, 0, 0, 0, CAST(0x9D630000 AS SmallDateTime), 1, 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 0, NULL)
INSERT [dbo].[Security_Module_Menu] ([Client_Company_ID], [Module_ID], [Menu_ID], [Reference_Menu_ID], [Menu_Name], [Menu_Description], [Menu_Type], [Tree_Level], [Target_URL], [Image_URL], [Confidentiality_Level], [Hotkey], [ToolTips], [Show_In_Toolbar], [Authentication_Level], [Has_Parameter], [Has_Charge], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type], [Priority]) VALUES (1, 1, 2, 0, N'Reports', N'', 0, 1, N'', N'', 0, N'', N'', 0, 0, 0, 0, 0, CAST(0x9D630000 AS SmallDateTime), 1, 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 0, NULL)
INSERT [dbo].[Security_Module_Menu] ([Client_Company_ID], [Module_ID], [Menu_ID], [Reference_Menu_ID], [Menu_Name], [Menu_Description], [Menu_Type], [Tree_Level], [Target_URL], [Image_URL], [Confidentiality_Level], [Hotkey], [ToolTips], [Show_In_Toolbar], [Authentication_Level], [Has_Parameter], [Has_Charge], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type], [Priority]) VALUES (1, 1, 3, 1, N'LookUp', N'', 2, 1, N'', N'', 0, N'', N'', 0, 0, 0, 0, 0, CAST(0x9D630000 AS SmallDateTime), 1, 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 0, NULL)
INSERT [dbo].[Security_Module_Menu] ([Client_Company_ID], [Module_ID], [Menu_ID], [Reference_Menu_ID], [Menu_Name], [Menu_Description], [Menu_Type], [Tree_Level], [Target_URL], [Image_URL], [Confidentiality_Level], [Hotkey], [ToolTips], [Show_In_Toolbar], [Authentication_Level], [Has_Parameter], [Has_Charge], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type], [Priority]) VALUES (1, 1, 4, 1, N'Bank Branch', N'', 2, 1, N'', N'', 0, N'', N'', 0, 0, 0, 0, 0, CAST(0x9D630000 AS SmallDateTime), 1, 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 0, NULL)
INSERT [dbo].[Security_Module_Menu] ([Client_Company_ID], [Module_ID], [Menu_ID], [Reference_Menu_ID], [Menu_Name], [Menu_Description], [Menu_Type], [Tree_Level], [Target_URL], [Image_URL], [Confidentiality_Level], [Hotkey], [ToolTips], [Show_In_Toolbar], [Authentication_Level], [Has_Parameter], [Has_Charge], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type], [Priority]) VALUES (1, 1, 5, 4, N'BO Category', N'', 2, 2, N'', N'', 0, N'', N'', 0, 0, 0, 0, 0, CAST(0x9D630000 AS SmallDateTime), 1, 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 0, NULL)
INSERT [dbo].[Security_Module_Menu] ([Client_Company_ID], [Module_ID], [Menu_ID], [Reference_Menu_ID], [Menu_Name], [Menu_Description], [Menu_Type], [Tree_Level], [Target_URL], [Image_URL], [Confidentiality_Level], [Hotkey], [ToolTips], [Show_In_Toolbar], [Authentication_Level], [Has_Parameter], [Has_Charge], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type], [Priority]) VALUES (1, 1, 6, 2, N'Cheque Type', N'', 3, 1, N'', N'', 0, N'', N'', 0, 0, 0, 0, 0, CAST(0x9D630000 AS SmallDateTime), 1, 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 0, NULL)
INSERT [dbo].[Security_Module_Menu] ([Client_Company_ID], [Module_ID], [Menu_ID], [Reference_Menu_ID], [Menu_Name], [Menu_Description], [Menu_Type], [Tree_Level], [Target_URL], [Image_URL], [Confidentiality_Level], [Hotkey], [ToolTips], [Show_In_Toolbar], [Authentication_Level], [Has_Parameter], [Has_Charge], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type], [Priority]) VALUES (1, 1, 7, 2, N'Stock Exchange', N'', 3, 1, N'', N'', 0, N'', N'', 0, 0, 0, 0, 0, CAST(0x9D630000 AS SmallDateTime), 1, 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 0, NULL)
/****** Object: Table [dbo].[Security_Module_Info] Script Date: 08/08/2010 01:04:44 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Security_Module_Info](
[Client_Company_ID] [smallint] NOT NULL,
[Module_ID] [tinyint] NOT NULL,
[Module_Name] [nvarchar](50) NULL,
[Module_Description] [nvarchar](200) NULL,
[Is_Active] [bit] NULL,
[Active_Date] [smalldatetime] NULL,
[Record_Status] [tinyint] NULL,
[Maker_ID] [smallint] NULL,
[Make_Date] [smalldatetime] NULL,
[Checker_ID] [smallint] NULL,
[Check_Date] [smalldatetime] NULL,
[Authorizer_ID] [smallint] NULL,
[Authorize_Date] [smalldatetime] NULL,
[Record_Action_Type] [tinyint] NULL,
CONSTRAINT [PK_Security_Module_Info] PRIMARY KEY CLUSTERED
([Client_Company_ID] ASC, [Module_ID] ASC)
) ON [PRIMARY]
GO
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 1, N'Basic Settings', N'', 1, CAST(0x9C790000 AS SmallDateTime), 1, 1, CAST(0x9D1103A9 AS SmallDateTime), 1, CAST(0x9D1103A9 AS SmallDateTime), 1, CAST(0x9D1103A9 AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 2, N'Configuration', N'', 1, CAST(0x9C790000 AS SmallDateTime), 1, 1, CAST(0x9C7903E9 AS SmallDateTime), 1, CAST(0x9C7903E9 AS SmallDateTime), 1, CAST(0x9C7903E9 AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 3, N'Customer Service', N'', 1, CAST(0x9C790000 AS SmallDateTime), 1, 1, CAST(0x9C7903EA AS SmallDateTime), 1, CAST(0x9C7903EA AS SmallDateTime), 1, CAST(0x9C7903EA AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 4, N'Trading', N'', 1, CAST(0x9C790000 AS SmallDateTime), 1, 1, CAST(0x9C7903E9 AS SmallDateTime), 1, CAST(0x9C7903E9 AS SmallDateTime), 1, CAST(0x9C7903E9 AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 5, N'Stock Exchange Settlement', N'', 1, CAST(0x9C790000 AS SmallDateTime), 1, 1, CAST(0x9C7903EB AS SmallDateTime), 1, CAST(0x9C7903EB AS SmallDateTime), 1, CAST(0x9C7903EB AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 6, N'Accounts', N'', 1, CAST(0x9C790000 AS SmallDateTime), 1, 1, CAST(0x9CD303D0 AS SmallDateTime), 1, CAST(0x9CD303D0 AS SmallDateTime), 1, CAST(0x9CD303D0 AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 7, N'Fund Management', N'', 1, CAST(0x9C790000 AS SmallDateTime), 1, 1, CAST(0x9C7903EB AS SmallDateTime), 1, CAST(0x9C7903EB AS SmallDateTime), 1, CAST(0x9C7903EB AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 8, N'Corporate Action', N'', 1, CAST(0x9C790000 AS SmallDateTime), 1, 1, CAST(0x9C7903EB AS SmallDateTime), 1, CAST(0x9C7903EB AS SmallDateTime), 1, CAST(0x9C7903EB AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 9, N'Daily Process', N'', 1, CAST(0x9C790000 AS SmallDateTime), 1, 1, CAST(0x9C7903EB AS SmallDateTime), 1, CAST(0x9C7903EB AS SmallDateTime), 1, CAST(0x9C7903EB AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 10, N'Mass Process', N'', 1, CAST(0x9CB70000 AS SmallDateTime), 1, 1, CAST(0x9CB70313 AS SmallDateTime), 1, CAST(0x9CB70313 AS SmallDateTime), 1, CAST(0x9CB70313 AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 11, N'Management', N'', 1, CAST(0x9CB70000 AS SmallDateTime), 1, 1, CAST(0x9CB70314 AS SmallDateTime), 1, CAST(0x9CB70314 AS SmallDateTime), 1, CAST(0x9CB70314 AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 12, N'Manual Share Receive', N'', 1, CAST(0x9C7E0000 AS SmallDateTime), 1, 1, CAST(0x9CD303D1 AS SmallDateTime), 1, CAST(0x9CD303D1 AS SmallDateTime), 1, CAST(0x9CD303D1 AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 13, N'Admin', N'', 1, CAST(0x9C840000 AS SmallDateTime), 1, 1, CAST(0x9CD303D0 AS SmallDateTime), 1, CAST(0x9CD303D0 AS SmallDateTime), 1, CAST(0x9CD303D0 AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 14, N'CDBL', N'', 1, CAST(0x9C760000 AS SmallDateTime), 1, 1, CAST(0x9CD303DF AS SmallDateTime), 1, CAST(0x9CD303DF AS SmallDateTime), 1, CAST(0x9CD303DF AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 15, N'Security Holding', N'', 1, CAST(0x9CE50000 AS SmallDateTime), 1, 1, CAST(0x9CE603E6 AS SmallDateTime), 1, CAST(0x9CE603E6 AS SmallDateTime), 1, CAST(0x9CE603E6 AS SmallDateTime), 1)
INSERT [dbo].[Security_Module_Info] ([Client_Company_ID], [Module_ID], [Module_Name], [Module_Description], [Is_Active], [Active_Date], [Record_Status], [Maker_ID], [Make_Date], [Checker_ID], [Check_Date], [Authorizer_ID], [Authorize_Date], [Record_Action_Type]) VALUES (1, 16, N'Test', N'shamim test work', 1, CAST(0x9DCA0000 AS SmallDateTime), 1, 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 1, CAST(0x9D630000 AS SmallDateTime), 1)
My required XML output is below:
<MyMenu>
<module Text="Basic Settings" ModID="Mod1" ModuleID="1" ShowInToolbar="False" MenuType="0" Perm="False">
<menu Text="Forms" MID="1-1" ParentID="Mod1" MenuDescription="Mod" ModuleID="1" ShowInToolbar="False" MenuType="0" Perm="False">
<Leaf Text="LookUp" MID="1-3" ParentID="1" MenuDescription="" ModuleID="1" ShowInToolbar="False" MenuType="2" Perm="False" LeafNode="true" SelPerm="False" InsPerm="False" UpPerm="False" DelPerm="False" TargetUrl="" ModuleMenuID="1-3">
</Leaf>
<submenu Text="Bank Branch" MID="1-4" ParentID="1" MenuDescription="" ModuleID="1" ShowInToolbar="False" MenuType="2" Perm="False">
<Leaf Text="BO Category" MID="1-5" ParentID="4" MenuDescription="" ModuleID="1" ShowInToolbar="False" MenuType="2" Perm="False" LeafNode="true" SelPerm="False" InsPerm="False" UpPerm="False" DelPerm="False" TargetUrl="" ModuleMenuID="1-5">
</Leaf>
</submenu>
</menu>
<menu Text="Reports" MID="1-2" ParentID="Mod1" MenuDescription="Mod" ModuleID="1" ShowInToolbar="False" MenuType="0" Perm="False">
<Leaf Text="Cheque Type" MID="1-6" ParentID="2" MenuDescription="" ModuleID="1" ShowInToolbar="False" MenuType="3" Perm="False" LeafNode="true" SelPerm="False" InsPerm="False" UpPerm="False" DelPerm="False" TargetUrl="" ModuleMenuID="1-6">
</Leaf>
<Leaf Text="Stock Exchange" MID="1-7" ParentID="2" MenuDescription="" ModuleID="1" ShowInToolbar="False" MenuType="3" Perm="False" LeafNode="true" SelPerm="False" InsPerm="False" UpPerm="False" DelPerm="False" TargetUrl="" ModuleMenuID="1-7">
</Leaf>
</menu>
</module>
</MyMenu>
My Security_Module_Info table just contains the Module
The information.Security_Module_Menu table contains all information, I want this table information in XML format. It's structure is very simple. XML generate on basis of this table's columns Module_ID, Menu_ID, Reference_Menu_ID.
I send sample input data here, Module_ID = 1 so in above XML output you can see Module tag comes only one time.
Reference_Menu_ID = 0 they generate Menu tag. And take place under the module like above. When Menu_ID, Reference_Menu_ID both are same then leaf tag generated, but if Leaf Have another leaf than parent leaf convert to SubMenu like above or below
<submenu Text="Bank Branch" MID="1-4" ParentID="1" MenuDescription="" ModuleID="1" ShowInToolbar="False" MenuType="2" Perm="False">
<Leaf Text="BO Category" MID="1-5" ParentID="4" MenuDescription="" ModuleID="1" ShowInToolbar="False" MenuType="2" Perm="False" LeafNode="true" SelPerm="False" InsPerm="False" UpPerm="False" DelPerm="False" TargetUrl="" ModuleMenuID="1-5">
</Leaf>
</submenu>
To generate this XML I wrote the code in Linq-to-XML but it fails.
var xml2 = new XElement("MyMenu",
from c in db.Security_Module_Menus
where c.ParentID == 0
select
new XElement("Module",
new XAttribute("Text", c.Menu_Name),
new XAttribute("Menu_ID", c.Menu_ID),
new XAttribute("Module_ID", c.Module_ID),
//new XAttribute("Menu_Type", c.Menu_Type),
new XAttribute("ParentID", c.ParentID),
from o in db.Security_Module_Menus
where o.ParentID == c.Menu_ID && o.Module_ID == c.Module_ID
select new XElement("Menu",
new XAttribute("Text", o.Menu_Name),
new XAttribute("Menu_ID", o.Menu_ID),
new XAttribute("Module_ID", o.Module_ID),
new XAttribute("ParentID", o.ParentID)
)
)
);
Please help me to build this XML and modify the Linq-to-XML code to make it work.