Getting null result from SQL Server stored proc which returns XML

25kuwait

New Member
I have a stored proc which returns a piece of XML. It works fine when I execute it from MS SQL Management Studio.I need to write some C# code to execute the proc and take the resulting XML and write it to a file.I wrote the code and it executes the proc (I checked using the profiler), and there is no error, but the XmlReader is empty.My code is something like:\[code\]using (SqlConnection c = new SqlConnection(-snip-)){c.Open();SqlCommand myCommand = new SqlCommand("sp_formfill_contract_xml", c);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.AddWithValue("@i_contract_id", frm_contract_id.Text.ToString()); System.Xml.XmlReader xmlr = myCommand.ExecuteXmlReader();xmlr.Read();while (xmlr.ReadState != System.Xml.ReadState.EndOfFile) { MessageBox.Show(xmlr.ReadOuterXml()); // do stuff with the XML snippet here } c.Close(); }\[/code\]As a comparison test I created a 2nd stored proc which returns a plain string containing the XML that the 1st proc generates, and the C# code receives that just fine, so long as I don't use the XML reader. So it's something to do with the kind of data that the XML proc is returning which makes it appear empty.Any ideas?-Sean
 
Back
Top