Troubleshooting SQL Server 2014 XML Problems
If you have problems with SQL Server 2014 XML, review this troubleshooting checklist to find
potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because many SQL Server 2014 XML bugs were fixed in SQL Server service packs, you should
install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was service
pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. The error 432 occurs when you try to use the Xml data type methods in
check constraints.
This error indicates that Xml data type methods are not supported in check constraints
anymore. You should drop the constraint or create a scalar user-defined function to wrap
the method invocation.
3. You can find out the error 527 when use XML types.
This is the error message text: “Implicit conversion between XML types constrained by
different XML schema collections is not allowed.” To work around this problem, you can
use the CONVERT function to run this query.
4. The error 3749 occurs when you try to drop XML index.
This error indicates that you cannot drop XML index using old ‘Table.Index’ syntax.
To drop XML index, you should use ‘Index ON Table’ syntax.
5. You can find out the error 6340 when you run a batch with Xml schema collection.
This is the error message text: “Xml schema collection ‘%.*ls’ referenced by table
variable ‘%.*ls’ has been dropped or altered during the execution of the batch.”
To work around this problem, you should re-run the batch.
6. The error 6624 occurs when you try to create XML document.
This error indicates that XML document could not be created because server memory
is low. To work around this problem, you can use the sp_xml_removedocument stored
procedure to release XML documents.
7. You can find out the error 6701 when you try to use XML index.
This is the error message text: “The version of the XML index that you are trying to
use is not supported anymore.” To work around this problem, you can drop and recreate
the XML index.
8. The error 9507 occurs when you use the XML data type method on a remote column.
This is the error message text: “The XML data type method on a remote column used in
this query can not be executed either locally or remotely.” To work around this problem,
you should rewrite the query.
9. You can find out the error 15389 when you try to use the sp_indexoption
stored procedure for XML indexes.
This error indicates that the sp_indexoption stored procedure is not supported for the
XML indexes. In this case, you should use the ALTER INDEX statement.
10. After you migrate from SQL Server 2005 to SQL Server 2014 the minute is overwritten
by the second in XML data.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 1
for SQL Server 2014. You can download the Cumulative Update package 1 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/2931693
11. When you run a query to update a table that has an XML calculated column, an access
violation exception occurs.
The query works correctly after you drop the constraints of the table. An access violation
occurs again when you try to re-add the constraints. This bug was first fixed in Cumulative
Update package 1 for SQL Server 2014. You can download the Cumulative Update package 1 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
12. SSIS 2014 XML validator only outputs “TRUE” or “FALSE” when you perform XML validation.
This bug was first fixed in Cumulative Update package 2 for SQL Server 2014. You can download
the Cumulative Update package 2 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2967546
After you apply the Cumulative Update package 2 for SQL Server 2014, a new task property
ValidationDetails appears. To obtain the detailed validation output in XML format, you have
to change the ValidationDetails value to true.
13. The error occurs when you load an XML document that has time zone information.
This is the error message text: “Specified cast is not valid”. This bug was first fixed
in Cumulative Update package 6 for SQL Server 2014. You can download the Cumulative
Update package 6 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3031047
14. The error occurs when you run an XML Validation Control Flow task on a large
input file in SSIS 2014.
This is the error message text: “Insufficient memory to continue the execution of the
program.” This bug was first fixed in Cumulative Update package 6 for SQL Server 2014.
You can download the Cumulative Update package 6 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3031047