how to convert Database Hierarchical Data to XML using ASP.net 3.5 and LINQ

i have a table with hierarchical structure. like this:

_{(source: aspalliance.com)}

and table data sho开发者_开发知识库wn here:

_{(source: aspalliance.com)}

this strategy gives me the ability to have unbounded categories and sub-categories.

i use ASP.net 3.5 SP1 and LINQ and MSSQL Server2005. How to convert it to XML? I can Do it with Dataset Object and ".GetXML()" method. but how to implement it with LINQtoSQL or LINQtoXML??? or if there is another simpler way to perform that? what is your suggestion? the best way? I searched the web but found nothing for .net 3.5 featuers.

Question Update

thnks to Murph but Now I have a New Problem. I want to Make a Dunamic SiteMap file in my Project. You Know the SiteMap file in ASP.net looks like this:

<siteMapNode url="~/Category?cid=0" title="Home"  description="Home">

    <siteMapNode url="~/Category?cid=1" title="a"  description="" />

    <siteMapNode url="~/Category?cid=2" title="b"  description="" >

        <siteMapNode url="~/Category?cid=3" title="c"  description="" />

        <siteMapNode url="~/Category?cid=4" title="d"  description="" />

    </siteMapNode>
</siteMapNode>

Main Problem is that according to Mr.Murph's Code the sub categories will nested in element. but in SiteMap Case we have no such a elements and all of categories and sub categories are nested in elements. how can I change Mr.Murph code to shape this Schema?

Ok very quickly (and thoroughly untested).

To "roll your own" XML using Linq to SQL and Linq to XML (which are generic .NET not ASP.NET specific) is fairly simple, this should be sufficient given a few assumptions which I'll detail after (now modified slightly as suggested):

void Main()
{
    DataContext dc = new DataContext();

    menuXML = new XDocument();
    XElement root = new XElement("menuxml",
        from m in dc.Menus
        where m.ParentID == null
        select GetMenuXML(m));

    menuXML.Add(root);
    // You've now got an XML document that you can do with as you need
    // For test you can save...
    menuXML.Save("filename.xml");
}

private static XElement GetMenuXML(Menu menu) 
{ 
    return new XElement("category",  
        new XAttribute("MenuID", menu.MenuID), 
        new XAttribute("Text", menu.Text), 
        new XElement("Description", menu.Description), 
        new XElement("menus", menu.Menus.Select(m => GetMenuXML(m)))); 
}

Ok, from the top

• I'm assuming a Linq to SQL data context called DataContext in which you've got the table mapped to Menus and that the parent/child relation is ChildMenus at the parent end.
• We create a new document
• We create a new element that we'll use as the root that we'll call menuxml
• We then do a linq to SQL query to select all the menu entries that don't have a parent and call GetMenuXML to output the XML for a single menu record (i.e that record and its children) for those entries.
• GetMenuXML outputs a single menu entry split betwixt attributes and elements the only slightly different thing is that I've used a lamba expression instead of the verbose syntax for generating the child menus - but if I've got it right (remember no test!) thats doing something along the lines of

from m in menu.ChildMenus select GetMenuXML(m)

If it works (!) you should get XML something like

<menuxml>
    <menu MenuID="1" Text="Product">
        <Description>A list of products</Description>
        <menus>
            <menu MenuID="6" Text="Background">
                <Description>Product Background</Description>
                <menus>
                    <menu MenuID="18" Text="Internet Restriction">
                        <Description>Internet Restriction</Description>
                        <!-- children if any would be here -->
                    </menu>
                    <menu MenuID="19" Text="Speed Solution">
                        <Description>Speed Solutions</Description>
                    </menu>
                </menus>
            </menu>
            <menu MenuID="7" Text="Background">
                <Description>Product Details</Description>
            </menu>
        </menus>
    </menu>
    <!-- rest of the top level menus -->
</menuxml>

thank you dear Murph. I used your code and it worked well. but as you say, you didn't test your code so GetMenuXML() had some mistakes. here is the correction:

private static XElement GetMenuXML(Menu menu)
{
    return new XElement("category", 
        new XAttribute("MenuID", menu.MenuID),
        new XAttribute("Text", menu.Text),
        new XElement("Description", menu.Description),
        new XElement("menus", menu.Menus.Select(m => GetMenuXML(m))));
}