This article was previously published under Q310378
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article refers to the following Microsoft .NET Framework Class Library namespace:
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.
To resolve this problem, use the ExecuteXmlReader method to read FOR XML queries. For additional information about how to use ExecuteXmlReader with SQL Server FOR XML queries, click the article numbers below to view the articles in the Microsoft Knowledge Base:
316016 How to use the ExecuteXmlReader method of the SqlCommand class in Visual Basic .NET
316701 How to use the ExecuteXmlReader method of the SqlCommand class in Visual C# .NET
307224 How to use XML in connected and disconnected ADO.NET applications
This behavior is by design.
Steps to reproduce the problem
Create a new Visual Basic Console application. Module1.vb is added by default.
Add the following code to the top of the module, above the Module Module1 declaration:
Imports SystemImports System.Data.SqlClient
Paste the following code into the Sub Main procedure of Module1:
Dim strSQL As String = "SELECT * FROM Orders FOR XML AUTO, ELEMENTS" Dim sbXML As New System.Text.StringBuilder() Dim dr As SqlDataReader Dim cn As New SqlConnection("data source=SqlServerName;user id=login;password=password;initial catalog=Northwind;") Dim cmd As New SqlCommand(strSQL, cn) cn.Open() dr = cmd.ExecuteReader 'Read the first Row dr.Read() 'Get the first Column (index 0) sbXML.Append(dr.GetString(0)) 'Uncomment the following lines to retrieve all of the results 'Do While dr.Read() ' sbXML.Append(dr.GetString(0)) 'Loop Console.WriteLine("Number of characters retrieved: " + sbXML.Length.ToString) Console.ReadLine() dr.Close() cn.Close()
Modify the following line of code in the preceding code sample to connect to the computer that is running SQL Server by using your actual login information:
Dim cn As New SqlConnection("data source=SqlServerName;user id=login;password=password;initial catalog=Northwind;")
Press F5 to build and run the project.
"Number of characters retrieved: 2033" is displayed in the Console window, indicating that only the first 2,033 characters of the XML contents were returned.
Press the ENTER key to close the Console window and return to the development environment.
Uncomment the following lines of code to retrieve all of the characters:
'Do While dr.Read()' sbXML.Append(dr.GetString(0))'Loop
Press F5 to rebuild and run the project.
All of the XML contents are retrieved, as demonstrated by the number displayed in the Console window.
Press ENTER to close the Console window and return to the development environment.