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

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 ...
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
FrankyDeMeyerAuthor Commented:
Well, there are 2 ways to reproduce the exception:

1. In SQL Server Management Studio, by running the repro SQL script (http://www.eazign.be/Temp/SqlRepro.sql.zip). 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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

FrankyDeMeyerAuthor Commented:
Thanks for tackling this rather convoluted question of mine!
FrankyDeMeyerAuthor Commented:
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.
For me, this problem was being caused by comments in my XSD that spanned more than one line.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.