Truncated XML responses from SQlCommand

A issue came up recently when an XML response from a SQL Command is truncated to 2033 chatacter when executing the command with ExecuteScalar.

We had some code that looked like this…

var dbCommand = GetCommand("name_of_sp");
string xml = dbCommand.ExecuteScalar();
var document = XDocument.parse(xml);

The stored procedure was using “FOR XML AUTO” to convert the response into XML. We’re still not sure why it truncated the XML but the best way to do this seems to be with the following code snippet.

var dbCommand = GetCommand("name_of_sp");
XmlReader xml = dbCommand.ExecuteXmlReader();