How do I return an XML dataset from MS SQL 2005 via a C# web service?

Hello all,

I am trying to setup some web services on an existing database which will be consumed by a Flex app.  I have created stored procedures in the database which generate the XML I want (via a select ... FOR XML ).  I have also setup a web service which is running just fine.  I can also consume my webserive fine in Flex.  What I don't know how to do is get the data from the stored procedure  in my database to my class in my web service.  Can anyone post a quick example?  I have posted below what I have figured out so far.  The stored procedures returns the XML I want perfectly in SQL management studio.  I just don't know how to get that out from my webservice and return it.  

Thanks,
[WebMethod]
        public string getMainView()
        {
            SqlConnection myCon = new SqlConnection(this.conString);
            myCon.Open();
            SqlCommand comm = new SqlCommand("EXEC dbo.select_main_search_view_XML",myCon);
            try
            {
                SqlDataReader myReader = comm.ExecuteReader();
                string result = null;
                while (myReader.Read())
                {
                    //??? What here?!
                }
 
            }
            catch
            {
 
            }
 
        }

Open in new window

kidl33tAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Did you see this example from MSDN:
Executing SQL Queries by Using the ExecuteXMLReader Method
http://msdn.microsoft.com/en-us/library/aa225800(SQL.80).aspx
0
 
kidl33tAuthor Commented:
Perhaps I should clear up my problem.  How do I return the XML from a stored procedure via a web service?
0
 
Anthony PerkinsCommented:
Since you are not returning a resultset, but rather a stream you may have more success if you use the appropriate .NET method.  So instead of the ExecuteReader I would recommend you use the ExecuteXmlReader and again since it is not a resultset there is no need to cycle through the rows.
0
 
kidl33tAuthor Commented:
acperkins:
Thanks the suggestion of the XML Reader.. I didn't know that existed.  How do I output my XML from the XmlReader?  None of the options in it seem intuitivly named?
0
All Courses

From novice to tech pro — start learning today.