Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


building menu from xml returned from stored procedure

Posted on 2009-04-22
Medium Priority
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
  • 3

Author Comment

ID: 24209428
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

CodeCruiser earned 2000 total points
ID: 24213116
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
ID: 24213206
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

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

Author Comment

ID: 24215299
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

580 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