Xml error: "Index was outside the bounds of the array."

Posted on 2008-11-07
Last Modified: 2012-05-05
I'll start with an easy way to reproduce the error I'm seeing:

Just download the following:

Contains only 1 file: SqlRepro.sql
Run this script on any SQL Server 2008 (I use the Standard version).

What the script does:
1. Create a new database: "FdmSqlRepro" (drops it first if already exists)
2. Create a schema collection: "MySchemaCollection"
3. Create a table: "TestTable", with a numeric "idx" column and a typed xml column "xData". The xml column is bound to the created "MySchemaCollection"
4. Insert 1 record in the table
5. Query the table (SELECT * FROM TestTable)

That's all!

The last step (query *) gives the error:
"Index was outside the bounds of the array."


Some more background:

Sql Server 2008 Standard:

To isolate the problem I'm having, I've created a simple Dbs with just one table, having an "int" field and an "xml" field.

The xml field is assigned to a SchemaCollection, so it is typed.
Updating the xml field with XML data works 99.999% of the times, but I have 2 specific XML fragments that systematically give me problems. Updating the xml field in the table with this data works fine without errors, however when I do a SELECT * from the table in the Management Studio, I get the error:

"An error occurred while executing batch. Error message is: Index was outside the bounds of the array."

Changing "SELECT xmlField FROM tab" to "SELECT CAST(xmlField AS nvarchar(MAX)) FROM tab" appears to give me the correct xml string, so the data is there!

Querying the data from .Net code gives the same error. Here's part of the stack trace:

[IndexOutOfRangeException: Index was outside the bounds of the array.]
System.Xml.XmlSqlBinaryReader.GetXsdKatmaiTokenLength(BinXmlToken token) +1207761
System.Xml.XmlSqlBinaryReader.ScanOverAnyValue(BinXmlToken token, Boolean attr, Boolean checkChars) +524
System.Xml.XmlSqlBinaryReader.ScanOverValue(BinXmlToken token, Boolean attr, Boolean checkChars) +159
System.Xml.XmlSqlBinaryReader.ImplReadData(BinXmlToken tokenType) +119
System.Xml.XmlSqlBinaryReader.ReadDoc() +589
System.Xml.XmlSqlBinaryReader.Read() +201

I get this exact problem on different machines: both the production Server 2003 and my Vista development PC.

I've been successfully using XML fields in SQL2005 for 3 years now, and have recently upgraded to SQL2008, so it's only on SQL 2008.

This has me completely puzzled. If I don't find a solution/work-around, I'll have to downgrade to SQL 2005....
This looks like a bug in SQL Server 2008.

What I'm looking for is a fix or work-around ...
Question by:FrankyDeMeyer
    LVL 49

    Expert Comment

    At the time of the exception, can't you examine the state of the XmlReader to see what node it was parsing at the time of the error?
    Or lacking that, remove parts of the XML until the error stops occurring.  
    Neither of these would solve the problem, but if you can isolate what it is about that specific XML that is causing the problem, then that's a starting point.

    Author Comment

    Well, there are 2 ways to reproduce the exception:

    1. In SQL Server Management Studio, by running the repro SQL script ( In that case, the error is shown in the SQL Mng Studio result window, so there's not much debugging I can do at that level (I think).

    2. When accessing the Dbs from .Net code. In that case, the exception is thrown by calling:
    SqlDataReader.GetSqlXml(i). That's a .Net library call. The index that I'm specifying is certainly OK.
    I have even enabled MS Library Source debugging, to see if I could step into the GetSqlXml() library call, but this is in MS assembly "System.Data.SqlClient", for which MS has not published the Source debugging symbols.

    So, in short, to answer your first question: I have no way to debug into the MS library code and find out at what XML node things go wrong. At least, not that I know of.


    Regarding your second suggestion: I have done that, and the weird thing is that the exception goes away when I "randomly" remove parts of the XML. There is absolutely no consistency in that. It can go away by removing some fragment in the middle, or at the start or end of the XML. Even changing some text can make the error appear and re-appear.

    We have thousands of records with different XML which are all OK, but it seems to be this specific combination of XML that provokes the error.

    Any further suggestions are very welcome! Thanks.
    LVL 49

    Accepted Solution

    The most common reason for an "out of bounds" exception is progamming that contains a sequence like
       if ( array(array.Length-1) = "x") ...
    that is, a situation when an index of -1 might end up being used (e.g., .when Length in the above example is 0).    I thoroughly understand that it is not your code that is causing the exception, but it's a place to start looking at the data.  Is there any item in this particular XML where an empty value goes in but a non-empty value might be expected?
    I'd also try making small changes to any element that is associated with a restricted string.  For instance, EmailPattern_Type and Guid_Type.  There may be a subtle bug in Microsoft's handling of these items, seen only when certain sequences of data are present.   A diagnostic aid would be to change these to simple xs:string and see if the problem goes away.
    In your shoes, I'd keep trying specific small changes until I narrowed it down to the minimum possible dataset with the minimum possible schema.  Once you have a tight/short, repeatable error, I'll bet that Microsoft tech support will take notice.

    Author Closing Comment

    Thanks for tackling this rather convoluted question of mine!

    Author Comment

    Thanks for looking into the schema and XML and for your helpful suggestions.

    Up to now, I've mostly looked into making changes to the actual XML to see which part the problem is related to.

    I've now started to make some simplifications to the XSD of the schemacollection itself, to find any dependencies.

    Substituting EmailPattern_Type and Guid_Type with xs:string didn't make any difference, but then I removed all the attributes which had a "fixed" value, and also removed any references to this attribute in the XML itself. This does seem to circumvent the problem with the XML examples I have tried.

    Although it doesn't really solve the actual problem, and may not be a 100% complete work-around, it is a potential route I can take.

    For this reason, and for the fact that it is now clear that it's an MS SQL Server 2008 bug, I accept your helpful advice as solution. Thanks.

    I am now also in contact with MS for detailed bug reporting, hopefully resulting in some patch from them.

    Expert Comment

    For me, this problem was being caused by comments in my XSD that spanned more than one line.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now