XML parsing: character 2033, unexpected end of input

Debugging today Program An error message is returned unexpectedly: XML Parsing: character 2033, unexpected end of input.

After a breakpoint is set, the error is found. Originally in the project databaseSQL Server 2005There is suchStored ProcedureFor example, select * From tbl_test for XML auto and elements. Then, I save the returned valuesDatasetTo the application layer.

However, the problem lies in the returned value. Because XML is returned, there is only one row in the fill to dataset. Now, because the returned value contains more than 2033 characters, then it is split into two rows. It can be imagined that if the returned result is similar to"<Tree>... </tree>"This XML, after being cut into two rows, is of course an error. See (Dataset value is cut ).

If the problem is found, the problem must be solved. This problem is not small, because many of my current projects use XML. If such errors occur, the consequence is ......

Do you want to change database fields? Is the XML return value less than 2033 characters ???

So I search and search again, there is a foreigner's post is good (http://aspnetresources.com/blog/executescalar_truncates_xml.aspx), also mentioned this problem, but his solution does not seem to be the best. It is usedExecutexmlreaderTo solve,CodeAs follows:

Connection. open ();
RDR = Command. executexmlreader ();
RDR. Read ();

While (RDR. readstate ! = Readstate. endoffile)
... {
Result+ =RDR. readouterxml ();
}

RDR. Close ();

Obviously, it is solved by looping and splicing strings. It seems to be okay, but it is always very troublesome and not ideal.

At this time, I am very sorry. In the comments of this post, I accidentally got the answer, hahaha.

The original article is as follows:

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

So I finally solved the problem. In this way, you only need to modify the stored procedure, and you do not need to modify the fields and code. It seems good. As for the principle of such a solution, I will study it again when I have time.