ExecuteScalar Truncates XML at 2,033 Characters
Posted in Development
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
- Don’t use
ExecuteScalarto read XML from SQL Server! Just don’t. - 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!
36 comments
Tony
on September 19, 2005
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!
vamsi
on November 3, 2005
I checked it
Jingye Luo
on December 1, 2005
Met the exact same problem today. Thanks for your research and clear description
crazyhuque
on January 25, 2006
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.
Howie
on February 13, 2006
Thanks!
Looping all the recs did the trick!
peter
on June 21, 2006
thanks for your research and succinct solution
Milan Negovan
on July 26, 2006
Glad I could help!
oleg
on August 29, 2006
Thank you very much. This article helped me alot.
RL
on September 18, 2006
Thank you for saving me a lot of time by this article!!!
Brent
on November 2, 2006
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.
Milan Negovan
on November 2, 2006
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.
Kevin
on November 6, 2006
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.
Rupert Hollom
on December 13, 2006
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());
}
CRIGenuios
on February 15, 2007
Thanks a lot! I tought I was the only with this problem. You're right "very little". You are God!!
Amol
on May 4, 2007
I discovered this problem today. This is not documented anywhere! Thanks for confirming my own findings.
Asen
on June 11, 2007
I discovered this problem the “hard way” last week.
No documentation on Microsoft site….
Yes ‘d go with the string builder….
Thomas
on July 5, 2007
Thanks,
You have saved me time.
Rob E.
on August 7, 2007
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.
Bijay Singh
on September 8, 2007
Good article.Saved my time.
Shan
on January 10, 2008
Excellent. Simply amazing
Ihar Ku
on February 26, 2008
Bravo!
Thanks!
hashName
on April 22, 2008
Thanks a lot!!!
I was dumbfounded when i came across this issue.really strange..
Ramya
on May 1, 2008
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.
Raghav
on July 11, 2008
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.
SachinS
on September 22, 2008
Face same problem ....
Full credit to your blog for saving our time.........
:)
dhonda
on September 29, 2008
You saved me all sorts of time. Thanks!
cubino
on April 28, 2009
headache gone... thanks for the fix...
Praveen
on July 16, 2009
Thanks a lot Brent. Your solution worked out excellently....
Maggy M.
on October 29, 2009
Thank you very much for posting this solution. It took me forever figuring out what was causing the problem. When the exception kept throwing at position 2034, I guessed it might be some sort of character limitation. The lack of documentation on this is very frustrating.
nappisite
on November 27, 2009
Just ran into this same issue myself. Thanks for confirming what I found.
Juan
on December 3, 2009
You are a legend. Thank you!
Fabrício Fujikawa
on April 16, 2010
Thanks for sharing. It worked perfectly for me. I'd like to mention that we must loop through the XmlReader in the scope we executed the command ExecuteXmlReader. I was using a function to return the XmlReader and wasn't able to loop through it completely outside the scope of the command object.
Thanks!
Richard Shadman
on July 15, 2010
FOR XML AUTO, TYPE worked great for me..
Here is a link to an explanation
http://msdn.microsoft.com/en-us/library/ms190025.aspx
Santosh Gaikwad
on April 11, 2005
NO comment.