[SQL] FOR XML Clause and ExecuteScalar()

When reterive data via ExecuteScalar() against a SQL statement with FOR XML Clause, you may find that return value is truncate at 2033 characters.

SQL Server 2005 seperates xml result into multi rows of 2033 characters each when FOR XML clause creates xml data greater than 2033 characters.

To avoid this behavior from impact you code, use other Execute() method instead and concate each row in code, or, in your stored procedure declare a variable in xml type, select this variable instead of select directly.

Declare @xml xml
set @xml = (Select …… for Xml raw,Elements)
select @xml