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

by

Troubleshooting Guide: SQL Server Error 6967 — Invalid Type Definition for XML Schema Collection

Why This Error Happens

This error occurs when registering or altering an XML Schema Collection in SQL Server, where a complexType definition redefines the mixed attribute inconsistently between a base type and its derived type. SQL Server’s XML schema validation engine strictly enforces W3C XSD rules, which state that unless the derivation method is restriction, the derived complexType must inherit the exact same mixed content setting (true/false) as its base type. This typically stems from a malformed or hand-edited XSD file, a schema migrated from a third-party system with looser validation, or an application that generates XML schemas dynamically without accounting for SQL Server’s strict XSD compliance.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection and Type

  • Run the following query to locate the schema collection referenced in the error message: SELECT name, xml_schema_namespace, schema_id FROM sys.xml_schema_collections WHERE name = '<CollectionName>';
  • Extract the full schema XML using SELECT xml_schema_namespace(N'dbo', N'<CollectionName>'); to inspect the raw XSD content.
  • Note the exact type name mentioned in the %s placeholder of Error 6967 — this identifies which complexType has the mixed mismatch.

Step 2: Correct the Mixed Attribute Mismatch in the XSD

  • Open the extracted XSD in a text editor or Visual Studio XML editor on your Windows 11 / Windows Server 2025 machine.
  • Locate the base <xs:complexType> definition and check its mixed attribute value (e.g., mixed="true").
  • Locate the derived <xs:complexType> that uses <xs:extension base="..."> and ensure its mixed attribute matches the base type exactly.
  • If the derivation method is <xs:restriction> instead of <xs:extension>, the mixed values are permitted to differ — confirm you are using the correct derivation method for your intended design.
  • Save the corrected XSD file to a known path, e.g., C:\SQLSchemas\CorrectedSchema.xsd.

Step 3: Drop and Re-register the XML Schema Collection

  • Since SQL Server does not allow in-place editing of schema collections with structural changes, drop the existing collection: DROP XML SCHEMA COLLECTION dbo.<CollectionName>;
  • Re-create it using the corrected file: CREATE XML SCHEMA COLLECTION dbo.<CollectionName> AS 'C:\SQLSchemas\CorrectedSchema.xsd'; (load the file contents into a variable first via PowerShell or sqlcmd if using osql/sqlcmd import).
  • If other tables or XML columns reference this schema collection, re-validate those columns after re-registration using ALTER TABLE <TableName> WITH CHECK CHECK CONSTRAINT ALL;

Step 4: Validate the Source Application’s Schema Generation Logic

  • If the XSD is generated dynamically (e.g., from Entity Framework, BizTalk, or a custom ETL tool), update the schema generation templates to enforce consistent mixed values across base and derived types.
  • Re-test schema generation in a staging environment before deploying to production SQL Server 2025 instances.

Alternative or Verification Steps

  • Verify the fix by re-running the original CREATE XML SCHEMA COLLECTION or ALTER statement that triggered Error 6967 — it should now complete without error.
  • As a quick alternative, validate the XSD independently using an external tool (e.g., xmllint --schema or Visual Studio’s XML Schema Explorer) before importing it into SQL Server, catching mixed attribute mismatches earlier in the development pipeline.