SQL Server 2005 PROBLEM: Using the ADO.NET DbCommand.ExecuteScalar() Method Truncates at 2033 Characters

This statement used with ExecuteScalar() will truncate at 2033 characters:

SELECT
    *
FROM
    Segment
,   [Document]
WHERE
    Segment.SegmentId = [Document].SegmentId
FOR XML AUTO, ELEMENTS, ROOT('SegmentTreeSet')

Solve this problem with this design:

DECLARE @xml XML

SET @xml = 
    (
        SELECT
            *
        FROM
            Segment
        ,   [Document]
        WHERE
            Segment.SegmentId = [Document].SegmentId
        FOR XML AUTO, ELEMENTS, ROOT('SegmentTreeSet')
    )

SELECT @xml

As of this writing, DbCommand.ExecuteScalar() returns a String object of the appropriate length.

This problem is chronicled in detail (for SQL Server 2000 and 2005) here:

http://www.aspnetresources.com/blog/executescalar_truncates_xml.aspx

mod date: 2007-01-16T23:16:13.000Z