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

by

Why This Error Happens

Error 2735 (“Cannot create primary xml index”) typically occurs when SQL Server attempts to build a primary XML index on a table, but the target column contains malformed XML data, or the schema collection bound to the XML column is missing or corrupted. This can also happen if the table lacks a required clustered primary key, since primary XML indexes depend on the base table’s clustering key to function correctly. Insufficient permissions on the schema or database can also trigger this failure in Windows Server 2025 / Windows 11 environments running SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify the Base Table Has a Clustered Primary Key

  • Primary XML indexes require the base table to have a clustered index, usually via a PRIMARY KEY constraint.
  • Run the following query to confirm:
SELECT i.type_desc, i.is_primary_key
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('YourTableName');
  • If no clustered primary key exists, create one before proceeding:
ALTER TABLE YourTableName ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (YourIDColumn);

Step 2: Validate the XML Data and Schema Collection

  • Check if the XML column is bound to a schema collection that may be missing or corrupted.
SELECT c.name, t.name AS schema_collection
FROM sys.columns c
JOIN sys.xml_schema_collections t ON c.xml_collection_id = t.xml_collection_id
WHERE c.object_id = OBJECT_ID('YourTableName');
  • If the schema collection is invalid, drop and recreate it using CREATE XML SCHEMA COLLECTION.
  • Validate existing XML data manually to rule out malformed entries:
SELECT * FROM YourTableName WHERE TRY_CAST(YourXMLColumn AS XML) IS NULL;

Step 3: Check Database and File Permissions

  • Open SQL Server Management Studio (SSMS) and confirm the service account (e.g., NT Service\MSSQLSERVER) has db_ddladmin or sysadmin rights on the target database.
  • On Windows Server 2025 / Windows 11, verify NTFS permissions on the SQL Server data directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) allow Full Control for the SQL Server service account.
  • Restart the SQL Server service after permission changes via services.msc or:
Restart-Service -Name MSSQLSERVER

Step 4: Recreate the Primary XML Index

  • Drop any existing broken index attempt:
DROP INDEX IF EXISTS IX_PrimaryXML ON YourTableName;
  • Recreate the primary XML index cleanly:
CREATE PRIMARY XML INDEX IX_PrimaryXML ON YourTableName (YourXMLColumn);

Alternative or Verification Steps

  • Confirm the index was created successfully by querying sys.xml_indexes and checking that using_xml_index_id is NULL (indicating it is the primary index).
  • If the error persists, run DBCC CHECKDB on the database to rule out underlying corruption, then retry index creation after a successful integrity check.