Can't read XML from Stored Proc on SQL Azure

JazC
JazC used Ask the Experts™
on
I have the following Stored Procedure


CREATE FUNCTION [dbo].[XML_GetTopicDetails]
(	@TopicDetailIDXML xml )
RETURNS xml
AS
BEGIN

	DECLARE @Result xml
	
	SET @Result = 
		(SELECT TopicID, Subject 
			ORDER BY Modified FOR XML PATH('Topic'))
	RETURN @Result
				
END

Open in new window



and I am trying to retrieve the xml to a c# web service that then dishes the xml, untouched to the client. I have the following i in the c# web service

[WebInvoke(UriTemplate = "/GetTopicList", Method="POST", BodyStyle=WebMessageBodyStyle.Bare)]
        public Stream GetTopicList(Stream stream)
        {
           
            StreamReader reader = new StreamReader(stream);
            string dataString = reader.ReadToEnd();

            _context = new taskngoEntitiesnew(connectionpath);
 
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.LoadXml(dataString);
            //Fixes and error when sending data to stored proc
            xmldoc.InnerXml = xmldoc.InnerXml.Replace("UTF-8", "UTF-16");
            
            //Check the Session by using the security class
 
            SqlConnection conn = new SqlConnection(SQLAzureDataConnection);
            SqlCommand cmd = new SqlCommand();
//            cmd.CommandText = "SELECT * FROM USERS FOR XML AUTO";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.XML_GetTopicDetails";

            cmd.Parameters.Add(new SqlParameter("@TopicIDXML", xmldoc.InnerText));

            conn.Open();
            cmd.Connection = conn;

            SqlDataReader xmlreader = cmd.ExecuteReader();

            StringBuilder xml = new StringBuilder();
            while (xmlreader.Read())
            {
                xml.AppendLine(xmlreader.GetString(0));
            }

            conn.Close();

            string FinalResult = xml.ToString();
            
            byte[] data = Encoding.ASCII.GetBytes(FinalResult);
            
            MemoryStream resultStream = new MemoryStream(data);

            return resultStream;
        }

Open in new window


I have tried over and over to get this working, as you can see I also just added a SQL Select * from Users FOR XML AUTO at one point, which returned xml fine, but I cannot get any data from the Stored Procedure. I have tried setting the CommandType to Text, but cannot get the data out. I know the stored proc works with the data I am sending as I can use this from an older Classic ASP web service page and it works fine.

I'm quite new to .NET programming, but I am sure this shouldn't be too hard... should it?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I managed to sort this, how do i remove the question?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial