How to fix Error 6931 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 6931 – XML Validation IDREF Constraint Check Failed

Why This Error Happens

Error 6931 occurs when SQL Server’s XML schema validation engine encounters an attribute of type IDREF or IDREFS that points to an ID value which does not exist anywhere in the XML instance document. This typically happens due to malformed XML data being inserted into a column typed with an XML schema collection, incomplete data migrations, or application-layer logic that generates XML fragments referencing nodes outside the current document scope. It can also surface after upgrading to SQL Server 2025 if legacy XML schema collections were registered with looser validation rules than the new engine enforces.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Document and Schema Collection

  • Run SELECT name, xml_collection_id FROM sys.columns WHERE xml_collection_id IS NOT NULL; to locate the affected column.
  • Extract the specific XML value causing the failure using a targeted SELECT on the primary key range reported in the SQL Server error log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025).
  • Cross-reference the attribute name and ID value from the error message (%.*ls placeholders) against the raw XML content.

Step 2: Validate and Repair the XML Instance

  • Export the problematic XML using SQL Server Management Studio (SSMS) 21 by right-clicking the result grid cell and selecting “Save Result As.”
  • Open the file and search for the IDREF attribute named in the error; confirm whether the corresponding ID-typed element/attribute is missing, misspelled, or was truncated during a prior ETL/import job.
  • Manually correct the XML fragment, or regenerate it from the source application, ensuring every IDREF has a matching ID in the same document instance.

Step 3: Re-register or Update the XML Schema Collection

  • If the schema itself is outdated, drop and recreate it using:
  ALTER XML SCHEMA COLLECTION [SchemaName] ADD 'updated_xsd_content_here';
  • Verify the .xsd file used for the collection correctly defines the xs:ID and xs:IDREF relationships and hasn’t been corrupted during transfer.
  • Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to clear any cached schema validation plans.

Step 4: Re-insert or Update the Corrected Data

  • Execute the UPDATE or INSERT statement again with the corrected XML payload.
  • Monitor the Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) for Event ID 6931 to confirm it no longer triggers.

Alternative or Verification Steps

  • Verify the fix by running SELECT XmlColumn.exist('//*[@id]') FROM YourTable; to confirm all referenced IDs resolve correctly within the document.
  • If the error persists across multiple records, consider temporarily unbinding the XML schema collection from the column (ALTER TABLE ... ALTER COLUMN XmlColumn XML; without the schema reference), cleaning the dataset in bulk, then re-binding the schema once all historical data is validated.