Don't mix ExecuteScalar with For XML EXPLICIT

In doing SQL Server data retrieval, it is very handy to issue a ‘For XML EXPLICIT’ Select query in the stored procedure. Then in the DB application code use ExecuteScalar to read it.

So far so good – until recently - I decide to increase a char field length by 10 bytes. Suddenly a few unit test cases break (thanks God, they break…). Return Xml string is truncated at 2033 characters.

This behaviour occurs because, for XML results greater than 2,033 characters in length, SQL Server returns the XML in multiple rows of 2,033 characters each.

This article
ASP.NET Resources - ExecuteScalar Truncates XML at 2,033 Characters investigates and describes the symptom in detail.