JazC
asked on
Can't read XML from Stored Proc on SQL Azure
I have the following Stored Procedure
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
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?
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
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;
}
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.