building menu from xml returned from stored procedure

Posted on 2009-04-22
Last Modified: 2012-05-06
I have a stored procedure in SQL Server that returns XML (via a 'FOR XML EXPLICIT') for a nav menu.

The nav menu (using a System.Web Menu control) in my ASP.NET page (coded in C#) uses an XmlDataSource.  

I need to get my XML from the stored procedure into the 'Data' field of the XmlDataSource control.

I have successfully created the C# code in the code behind page to call the stored procedure via an OleDb Command object.

My issue is do I use ExecuteReader, ExecuteScalar or something else?  I cannot seem to get the xml to be xml in the code.  If I run the stored procedure in query analyzer it works great in returning the xml to the results pane.  But in the code no go.
Question by:stee1hed

    Author Comment

    Before anyone questions why I'm doing it this way... The nav menu needs to be dynamic to each user logged into the system.  I do not want static menu files for each user on the server.  Instead, menu information will be stored in a Session variable (as xml).
    LVL 83

    Accepted Solution

    You can use the XMLReader object to retrieve xml
    XmlReader rdr = cmd.ExecuteXmlReader();
    if (rdr.Read())

    Check this link
    LVL 96

    Expert Comment

    by:Bob Learned
    If you are selecting a single value, I would think that you could use ExecuteScalar, and store the XML string that you get from the stored procedure, and set the XmlDataSource.Data property.

    XmlDataSource.Data Property

    Author Closing Comment

    I found this as well and it works like a champ!

    Author Comment

    CodeCruiser's example and link helped me piece together what I found along the same lines and it's working how I wanted.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now