Link to home
Start Free TrialLog in
Avatar of JazC
JazCFlag for New Zealand

asked on

Can't read XML from Stored Proc on SQL Azure

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?
ASKER CERTIFIED SOLUTION
Avatar of JazC
JazC
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial