ExecuteScalar() not returning full contents of cell

Question

I have a stored procedure which returns an XML string. When I copy this directly from the table, I get everything exactly as I expect it.

However, when I try to run this stored procedure with C#, the ExecuteScalar() returns only 46 lines of the 70 lines I'm expecting.

Here is the code I'm using:

using (SqlConnection con = new SqlConnection("Data Source=TEST;Initial Catalog=BMRSK;Integrated Security=True"))
{
    using (SqlCommand buildXML = new SqlCommand("usp_BUILD_RISKCALC_XML", con))
    {
        buildXML.CommandType = CommandType.StoredProcedure;

        con.Open();

        XmlDocument xdoc = new XmlDocument();
        xdoc.LoadXml((string)buildXML.ExecuteScalar());
        xdoc.Save("Test.xml");
    }
}

This is the exception I get from the line

xdoc.LoadXml((string)buildXML.ExecuteScalar());

An unhandled exception of type 'System.Xml.XmlException' occurred in System.Xml.dll

Additional information: Unexpected end of file has occurred. The following elements are not closed: ARGUMENT, ARGUMENT-LIST, OPERATION, OPERATION-LIST, RISKCALC. Line 1, position 2034.

Any ideas would be greatly appreciated. Thank you.

Answer 1

ExecuteScalar only reads 2033 characters. Use ExecuteXmlReader instead.

 private static void BuildXML()
 {
     using (SqlConnection con = new SqlConnection("Data Source=TEST;Initial Catalog=BMRSK;Integrated Security=True"))
     {
         using (SqlCommand buildXML = new SqlCommand("usp_BUILD_RISKCALC_XML", con))
         {
              buildXML.CommandType = CommandType.StoredProcedure;

              con.Open();

              XmlReader xmlReader = buildXML.ExecuteXmlReader();
              XmlDocument xdoc = new XmlDocument();
              xdoc.Load(xmlReader);
              xdoc.Save("Test.xml");
         }
     }
}