I have an xml-file that contains my menu. Now I wan't to have the menu in a database and when I add a new item to the menu in the database I want the application to write a new xml-file with all the new items. I can build everything but I can't write the SQL to this. My database contains these fields:

menuID
menuText
menuPath
menuParent

1, HOME, ~/default.aspx, 0
2, Contact, ~/contact.aspx, 1
3, Categories, ~/categories.aspx, 1
4, Cars, ~/categories/cars.aspx, 3
5, Trains, ~/categories/trains.aspx, 3

I want my xml-file to look like this
<root>
  <child title="HOME" url="\default.aspx">
  <newChild title="Contact" url="~/contact.aspx" />
    <newChild title="Categories" url="~/categories.aspx">
      <newChild2 title="Cars" url="~/categories/cars.aspx" />
      <newChild2 title="Trains" url="~/categories/trains.aspx" /
    </newChild>
  </child>
</root>

How can I write an SP so i can do this? I know how to write an xml-file and all that stuff, it's just the SP i don't know how to write.

 I suugest that you download a copy of SQL Books On Line ( a free download from Microsoft) and look at the FORXML directive. After a few hours study you will be able to write the required TSQL to go into your SP.

Thank you for your answer. I think I have to pass on this one, I dont understand how to do this.

Can you please give me a clue?

See this query   i have used "For XML Auto" .

if (!IsPostBack)
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                conn.Open();
                string strText = "SELECT Environments.Env_ID, Environments.Env_Type,Applications.App_ID,Applications.App_Name, Layers.Layer_ID,Layers.Layer_Type,Servers.Server_ID, Servers.Server_Name,Env_Footprints.Server_Instance FROM Environments LEFT JOIN (((Env_Footprints LEFT JOIN Applications ON Env_Footprints.App_ID = Applications.App_ID) LEFT JOIN Layers ON Env_Footprints.Layer_Type = Layers.Layer_Type)INNER JOIN Servers ON Env_Footprints.Server_ID = Servers.Server_ID) ON Environments.Env_ID = Env_Footprints.Env_ID ORDER BY Environments.Env_Type, Applications.App_Name, Env_Footprints.Layer_Type,Servers.Server_Name 
 For Xml Auto";
               
  SqlCommand sqlcmd = new SqlCommand(strText, conn);
               
                DataSet ds = new DataSet();
                XmlReader xrdr = sqlcmd.ExecuteXmlReader();
              
                StreamWriter sw = new StreamWriter("C:\\Sid\\Upload\\TreeviewXMLFile.xml");
                sw.WriteLine("<EnvironmentsFootPrints>");
               
                xrdr.ReadToFollowing("Environments");
                while (!xrdr.EOF)
                {
              
                 
                   sw.WriteLine(xrdr.ReadOuterXml());
              
                }
                sw.WriteLine("</EnvironmentsFootPrints>");
                sw.Close();
                xrdr.Close();
                conn.Close();
             

             
               
                XmlDataSource1.DataFile = "C:\\Amrita\\Upload\\TreeviewXMLFile.xml";

}

 See if the below thread can be helpful.

http://forums.asp.net/p/1182514/2008292.aspx 

Thank you for your answers.

I have rebuilded my db. No I have two tables menu and subMenu.

(menu)
menuID
menuText
menuToolTip
menuPath

(subMenu)
subMenuID
parentID (FK)
subMenuText
subMenuToolTip
subMenuPath

Can you help me build a Sql query so I can get it like this?

<menu title="HOME" url="~/default.aspx" tool="blabla bla bla">
  <subMenu title="Filebrowser" url="~/filebrowser.aspx" tool="blabla" />
  <subMenu title="categories" url="~/categories/default.aspx" tool="blabla">
    <subMenu title="Cars" url="~/categories/cars" tool="blabla" />
    <subMenu title="Trains" url="~/categories/trains" tool="blabla" />
  </subMenu>
  <subMenu title="Contact" url="~/contact.aspx" tool="blabla" />
</menu>

 You could just have a menu table like this:

Id       - Identity integer primary key
ParentId - Nullable Int FK to menu table - null value indicates a parent record
Text
ToolTip
Path

Yes I konw, but I thought i should be easer to biuld the Sql query if I had two tables. It's the sql query I can't build. I have never used "FOR XML" before. I have red in Books Online but I don't understand how I should do:(

I think I got it right now. Now it's two other problems I have. How can I retorn the result from the spand how do I receive it in my dbclass so I can build anxml-file or what I want to do? I'm not sure this i correct beacuse I have not seen all file. It's correct as muck I can se in the output window.

CREATE PROC doXml
@xmlFile XML OUTPUT
as
select 1 as tag,
null as parent,
m.menuText AS [menuItem!1!title],
m.menuToolTip as [menuItem!1!tool],
m.menuPath as[menuItem!1!url],
null as [subMenuItem!2!title],
null as [subMenuItem!2!tool],
null as [subMenuItem!2!url]
FROM menu m
UNION ALLselect 2 as tag,
1 as parent,
null,
null,
null,
sm.subMenuText,
sm.subMenuToolTip,
sm.subMenuPath
from submenu sm
WHERE exists(select * from menu WHERE menuID = sm.parentID)
FOR XML EXPLICIT

I really think I have done it:) This is my code, if you se something I can change please write and tell me. I will not mark this ar solved just yet if it's not working as I expect. Now I will build an xsl-file to format the whole thing.

SP
ALTER PROC doXml
as
select 1 as tag,
null as parent,
m.menuText AS [menuItem!1!title],
m.menuToolTip as [menuItem!1!tool],
m.menuPath as[menuItem!1!url],
null as [subMenuItem!2!title],
null as [subMenuItem!2!tool],
null as [subMenuItem!2!url]
FROM menu m
UNION ALL
select 2 as tag,
1 as parent,
null,
null,
null,
sm.subMenuText,
sm.subMenuToolTip,
sm.subMenuPath
from submenu sm
WHERE exists(select * from menu WHERE menuID = sm.parentID)
FOR XML EXPLICIT

Well, this is not working att all:( All childnodes comes under the last node. I really need help with this

ALTER PROC doXml
as
select
1 as tag ,
null as parent ,
[text] as [menuItem!1!text] ,
description as [menuItem!1!description] ,
menuPath as [menuItem!1!path],
null as [menuItem!2!text] ,
null as [menuItem!2!description],
null as [menuItem!2!path]
from menuDB
where parentid = -1
union all
select
2 ,
1 ,
m1.[text] ,
m1.description ,
m1.menuPath,
m2.[text] ,
m2.description,
m2.menuPath
from menuDB m1 , menuDB m2
where m2.parentid = m1.menuID
for xml explicit

 Using

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MenuDB](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [Description] [nvarchar](50) NOT NULL,
    [MenuPath] [nvarchar](50) NOT NULL,
    [Text] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_menuDB] PRIMARY KEY CLUSTERED
(
    [Id] 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
ALTER TABLE [dbo].[MenuDB]  WITH CHECK ADD  CONSTRAINT [FK_menuDB_menuDB] FOREIGN KEY([ParentId])
REFERENCES [dbo].[MenuDB] ([Id])
GO
ALTER TABLE [dbo].[MenuDB] CHECK CONSTRAINT [FK_menuDB_menuDB]

svetsarn:

Inow have gone back to try with one table, but I got same result now. All childnodes comesunder the last node.

You need to provide the order by clause in order to get the correct results back.  Please visit the below discussion if you have not yet.

http://forums.asp.net/p/1182514/2008292.aspx

 The stored procedure

ALTER PROC doXml
as
select
1 as tag ,
ParentId as parent ,
CONVERT(VARCHAR(10), Id) + '-' AS [menuItem!1!X],
[text] as [menuItem!1!text] ,
[description] as [menuItem!1!description] ,
menuPath as [menuItem!1!path],
null as [menuItem!2!text] ,
null as [menuItem!2!description],
null as [menuItem!2!path]
from menuDB
where ParentId Is Null
union all
select
2 ,
m1.ParentId ,
CONVERT(VARCHAR(10), m2.ParentId) + '-' + CONVERT(VARCHAR(10), m2.Id) AS [menuItem!1!X],
m1.[text] ,
m1.[description] ,
m1.menuPath,
m2.[text] ,
m2.description,
m2.menuPath
from menuDB m1 , menuDB m2
where m2.parentid = m1.Id
ORDER BY 3
for xml explicit
GO
EXEC dbo.doXml

gave

<menuItem X="1-" text="Text A" description="Root A" path="A" />
<menuItem text="Text A1" description="Branch A1" path="A1" />
<menuItem text="Text A2" description="Branch A2" path="A2" />
<menuItem X="2-" text="Text B" description="Root B" path="B" />
<menuItem text="Text B1" description="Branch B1" path="B1" />
<menuItem text="Text B2" description="Branch B2" path="B2" />

This is at least ordered better

It might be best to an explicit OrderBy column to order children within parent