Selective Xml Index causing Insert Error: string or binary data would be truncated. the statement has been terminated

Question

I was receiving the error:

string or binary data would be truncated. the statement has been terminated.

Inserting XElement data into a SQL Server 2012 Xml column from C# via Linq-to-SQL was causing the error.

After running Profiler and other tools, I was able to find that one of the attributes in the XML was causing the error:

I was using this in the C# code:

XmlDocument.SchemaVersion = System.Reflection.Assembly.GetAssembly(typeof(Data.Schema.Document)).GetName().Version.ToString();

This should give an output something like

<tst:Root xmlns:tst="http://tempuri.org/some.xsd" SchemaVersion="0.2.1234.12345" RevNumber="1">
...
...
</tst:Root>

But it is causing the error to be thrown, but this:

SchemaVersion="3" or SchemaVersion"0.2.123.1" works fine if I test like:

XmlDocument.SchemaVersion = "0.2.123.1"

There is not SchemaCollection on the XML column in SQL Server to force the error.

Does SQL Server somehow enforce the SchemaVersion attribute to be a certain length without being user defined, or why would the C# statement I was using cause the error?

Edit:

Added Sample Sql That is failing and working when manually testing

Fails:

Insert into XmlTable(XmlId, XmlColumn)
Values('1', '<tst:Root xmlns:tst="http://tempuri.org/some.xsd" SchemaVersion="0.2.1234.12345" RevisionNumber="1" />')

Works:

Insert into XmlTable(XmlId, XmlColumn)
Values('1', '<tst:Root xmlns:tst="http://tempuri.org/some.xsd" SchemaVersion="0.2.1234.1" RevisionNumber="1" />')

KeyboardFriendly KeyboardFriendly · Accepted Answer · 2013-07-03T19:16:13

The datatype specified in the Selective Xml Index was causing the error:

string or binary data would be truncated. the statement has been terminated.

Ill post the index so that if anyone else has the issue, maybe it will help.

Check the length against the length specified and changing it resolved the issue for me.

CREATE SELECTIVE XML INDEX [xsi_XmlTable] ON [dbo].[XmlTable]
(
    [XmlColumn]
)
WITH XMLNAMESPACES
(
DEFAULT 'http://tempuri.org/some.xsd'
)

FOR
(
[1] = '/Root' as XQUERY 'node()', 
[2] = '/Root/@SchemaVersion' as SQL [nvarchar](10) SINGLETON 
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO

Selective Xml Index causing Insert Error: string or binary data would be truncated. the statement has been terminated

1 Answers