ExecuteScalar Truncates XML at 2,033 Characters

SQL Server 2000 makes manipulating XML a breeze. It added the FOR XML [RAW|AUTO|EXPLICIT] clause which allows you to convert result sets into several flavors of XML documents. In the same vein, OpenXML was a great step forward in helping with batch processing. For example, if you import 10,000 users into your database it’s more efficient to put them into an XML document and send it to SQL Server to parse and insert them all at once, rather that shove them one row at a time.

I’ve used XML features of SQL Server for over two years now and loved them. Today I ran into a weird issue which somehow evaded me all this time. I figured I’d research it and pass on a warning.

The Inside Scoop

I have a stored proc which does a simple SELECT and returns it as an XML fragment. The downside of using FOR XML EXPLICIT is that it doesn’t return a well-formed XML document, and Books Online explicitly warn you:

When you specify EXPLICIT mode, you must assume the responsibility for ensuring that the generated XML is well-formed and valid.

Knowing that I’m going to read an XML fragment as a string and wrap an opening and closing tag around it (<Tree>...</Tree>) I settled for ExecuteScalar. I was in for a big surprise when, on one particular live instance of our product, the XML string came back… truncated! Stepping through code with the debugger confirmed that the string was chopped at exactly 2,033 characters!

A quick search in Google groups turned up very little. Nobody had answers. And then I stumbled across a Microsoft’s KB article Q310378: XML Data Is Truncated When You Use SqlDataReader, and all of a sudden it became clear:

When you read Extensible Markup Language (XML) data from Microsoft SQL Server by using the SqlDataReader object, the XML in the first column of the first row is truncated at 2,033 characters. You expect all of the contents of the XML data to be contained in a single row and column.

This behavior 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.

The keyword here is multiple. If you call ExecuteScalar you have no way of getting anything but the very first column of the very first row, so this method is a dead end.

Another KB article, Q316701: Use the ExecuteXmlReader Method of the SqlCommand Class in Visual C# .NET shows how to bypass this problem by reading in a loop until all rows are read. I ended up with this:

connection.Open ();
rdr = command.ExecuteXmlReader ();
rdr.Read ();

while (rdr.ReadState != ReadState.EndOfFile) 
{
 result += rdr.ReadOuterXml ();
}

rdr.Close ();

For greater efficiency I’d use a StringBuilder here to concatenate chunks of text.

Lessons Learned

1. Don’t use ExecuteScalar to read XML from SQL Server! Just don’t.
2. If you read XML by hand (as shown above) read it in loops because it comes sliced 2,033 characters at a time.

Conclusion

Here’s something odd: I looked through Microsoft ADO.NET (Core Reference) and Applied XML Programming for Microsoft .NET and found no mention of this gotcha. This “little” issue is hardly documented in other books or on MSDN at all! Now, c’mon! This is the kind of warning I expect to see in books!

NO comment.

Thanks for posting this comprehensive overview of the problem. I've been dumbfounded as to why my simple ExecuteScalar call wasn't working. Thanks to your blog entry, I've got it working now!

I checked it

Met the exact same problem today. Thanks for your research and clear description

Thanks for the comment..it really helped my project...i spent the whole day figuring out what was the problem....now it 2:26am...and i'm done....thanks again.

Thanks!
Looping all the recs did the trick!

thanks for your research and succinct solution

Thanks Milan!

Glad I could help!

Thank you very much. This article helped me alot.

Thank you for saving me a lot of time by this article!!!

I can't explain this, but we found another way to get around the issue. My original stored procedure just selected XML results directly like this:

SELECT * FROM tbl_Test FOR XML AUTO, ELEMENTS

I changed it to select the XML results into an XML variable and then selected those to return and the truncation issue was gone.

DECLARE @xml XML
SET @xml = (SELECT * FROM tbl_Test FOR XML AUTO, ELEMENTS)
SELECT @xml

Not sure exactly why this makes a difference but this allows us to keep using the one line ExecuteScalar instead of looping, which is nice.

Are you using SQL 2K5 this time around? In SQL Server 2000 XML was a bolt-on which is why, perhaps, the issues. SQL Server 2005 should be much better in terms of hanling XML now that there's an xml type.

Thank you both Brent and Milan! I ran into this limitation today and was able to confirm that Brent's suggestion is working great on SQL 2005 Express.

Thanks for this, but I found that the XML was being parsed as I read it in with ReadOuterXML() and this was causing errors as some of the tags where being split. I ended up just reading the stuff into a DataTable and iterating through the rows, like this:

StringBuilder sb = new StringBuilder(1000);
DataTable dsDisplayTree = displayTree.ExecuteDataTable();

foreach (DataRow drTree in dsDisplayTree.Rows)
{
sb.Append(drTree.ItemArray[0].ToString());
}

Thanks a lot! I tought I was the only with this problem. You're right "very little". You are God!!

I discovered this problem today. This is not documented anywhere! Thanks for confirming my own findings.

I discovered this problem the “hard way” last week.
No documentation on Microsoft site….
Yes ‘d go with the string builder….

Thanks,

You have saved me time.

You want to force the resultas to use the XML data type built in the SQL Server (2005). To do that, specify "type" at the end of your "FOR XML" clause.

You'll find that the XML data types does not truncate results.

Good article.Saved my time.

Excellent. Simply amazing

Bravo!
Thanks!

Thanks a lot!!!
I was dumbfounded when i came across this issue.really strange..

Thanks much. I ran into the same issue today and the blog helped to fix the issue in few mins. Thanks to Brent, I like the XML datatype approach in SQL Server.

Thanks for the excellent article Milan. I ran into the same issue with SQL Server 2005 and fixed by using Rob's solution. I think it is the most cost effective solution for this issue with SQL Server 2005.

Anyway thanks for the help.

Face same problem ....
Full credit to your blog for saving our time.........
:)

You saved me all sorts of time. Thanks!