The XML data row is truncated at 2,033 characters when you use the SqlDataReader object

Article translations Article translations
Article ID: 310378 - View products that this article applies to.
This article was previously published under Q310378
This article refers to the following Microsoft .NET Framework Class Library namespace:
  • System.Data.SqlClient
Expand all | Collapse all

On This Page

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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

STATUS

This behavior is by design.

MORE INFORMATION

Steps to reproduce the problem

  1. Create a new Visual Basic Console application. Module1.vb is added by default.
  2. Add the following code to the top of the module, above the Module Module1 declaration:
    Imports System
    Imports System.Data.SqlClient
    					
  3. 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()
    					
  4. 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;")
    					
  5. 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.
  6. Uncomment the following lines of code to retrieve all of the characters:
    'Do While dr.Read()
    '    sbXML.Append(dr.GetString(0))
    'Loop
    					
  7. 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.
  8. Press ENTER to close the Console window and return to the development environment.

Properties

Article ID: 310378 - Last Review: March 13, 2006 - Revision: 2.1
APPLIES TO
  • Microsoft ADO.NET 2.0
  • Microsoft ADO.NET (included with the .NET Framework)
Keywords: 
kbtshoot kbdatabase kbprb KB310378
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.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com