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)
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:
Index was outside the bounds of the array.]
nXmlToken token) +1207761
en token, Boolean attr, Boolean checkChars) +524
nXmlToken token, Boolean attr, Boolean checkChars) +159
XmlToken tokenType) +119
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 ...