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

by

Why This Error Happens

Error 2221 occurs when SQL Server validates XML data against a registered XML Schema Collection (XSC) and encounters an attribute that is not defined in the corresponding complex type definition. This typically happens due to a namespace prefix mismatch, an outdated or incomplete schema collection that hasn’t been updated to reflect new attributes, or a corrupted schema import where the attribute declarations were stripped during migration. It is a data validation issue rather than a service-level failure, meaning the SQL Server engine is functioning correctly but is strictly enforcing XSD constraints that the incoming XML document violates.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

  • Run the following query to locate the exact schema collection referenced in the error and inspect its current XML structure:
SELECT name, xml_schema_namespace, [definition] 
FROM sys.xml_schema_collections xsc
CROSS APPLY (SELECT CONVERT(xml, xsc.xml_schema_namespace) AS [definition]) d
WHERE name = 'YourSchemaCollectionName';
  • Cross-reference the attribute name mentioned in the error message (e.g., @ns:AttributeName) against the <xs:complexType> definitions in the output.

Step 2: Validate the Incoming XML Namespace Prefix

  • Open the source XML payload being inserted or updated and check the namespace declaration (xmlns:ns="...") at the root level.
  • Ensure the prefix used in the attribute (e.g., ns:AttributeName) exactly matches the target namespace registered in the SQL Server XML Schema Collection; case sensitivity and trailing slashes in URIs are common culprits.
  • If the prefix is incorrect, update the client application or ETL job generating the XML to use the correct namespace URI.

Step 3: Update or Alter the XML Schema Collection

  • If the attribute is legitimately new and missing from the schema (rather than a typo), use ALTER XML SCHEMA COLLECTION to add the missing attribute definition without dropping the existing collection:
ALTER XML SCHEMA COLLECTION dbo.YourSchemaCollectionName ADD 
N'<xs:schema targetNamespace="your-namespace" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:attribute name="AttributeName" type="xs:string"/>
</xs:schema>';
  • Verify that no dependent stored procedures or CLR types will break due to the schema version change.

Step 4: Clear Cached Execution Plans

  • Since XML schema validation plans can be cached, flush the procedure cache for the specific database to force SQL Server to re-read the updated schema collection:
DBCC FREEPROCCACHE;
  • Restart the SQL Server service via services.msc on Windows Server 2025 only if the issue persists after clearing the cache, as a full restart guarantees schema metadata is reloaded from disk.

Alternative or Verification Steps

  • Re-run the original INSERT/UPDATE statement or application transaction that triggered Error 2221; if it completes without throwing the XML validation error, the schema collection has been successfully updated.
  • As an alternative, if modifying the schema is not feasible, use TRY_CAST or wrap the XML insertion logic in a TRY...CATCH block in T-SQL to bypass strict typed XML columns and store the data as untyped xml temporarily until the schema mismatch is resolved.