XML Generation

See more:

C#

ASP.NET

I have a table stucture in below


<pre> USE [MOSS_Sample]GO
/****** Object: Table [dbo].[TT_FORMS_ASSIGIN] Script Date: 10/30/2010 10:28:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TT_FORMS_ASSIGIN]( [MAIN_ID] [int] NOT NULL, [SUB_ID] [int] NOT NULL, [FORM_ID] [int] NOT NULL, [FORM_NAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FORM_HEADER] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FORM_PATH] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [READ_WRITE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ROLE_ID] [int] NOT NULL, [ICON_PATH] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [INSERTED_BY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [INSERTED_DATE] [datetime] NULL, [MODEFIED_BY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MODEFIED_DATE] [datetime] NULL, CONSTRAINT [PK_TT_FORMS_ASSIGIN] PRIMARY KEY CLUSTERED
( [MAIN_ID] ASC, [SUB_ID] ASC, [FORM_ID] ASC, [ROLE_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO
SET ANSI_PADDING OFF
</pre>


I need to convert above table in to below xml format

XML Example:


<root >
<type value="Administrator">
<ActionType value="Masters">
<ActionItem value="Head Office" url="~/Master/HeadOffice.aspx" displayText="Head Office" >
</ActionItem>
<ActionItem value="Area Office" url="~/Master/HeadOffice.aspx" displayText="HeadOffice">
</ActionItem>
</ActionType>
<ActionType value="Loans">
<ActionItem value="BulkLoans" url="~/Loans/BulkLoans.aspx" displayText="BulkLoans" >
</ActionItem>
<ActionItem value="MemberApplication" url="~/Loans/MemberApplication.aspx" displayText="MemberApplication">
</ActionItem>
</ActionType>
</type>
</root >
in below following sentences make u underrstant my problem in generating xml based on three column combination .
am explaning the formate which i need for generating xml

inabove table if column values (mainId,SubId,FormId)=(1,0,0) for this combination xml main node should be in this formate

<type value="Administrator>
if(maiId,SubId,FormId)=(1,1,0) for combination xml Middle node should be in this formate
<ActionType value="Masters">
if(maiId,SubId,FormId)=(1,1,1) for combination xml inner node should be in this formate
<actionitem value="Head Office" url="~/Master/HeadOffice.aspx" displaytext="Head Office">
tag (bulkLoans)
url and displayname come from table if(1,1,1)

i nead to generate all xml in above combinations wise

form_header will give value