I have a table stucture in below format
<pre lang="sql">USE [MOSS_Sample]
GO
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
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