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

by

Troubleshooting Guide: Error 9536 in Microsoft SQL Server 2025

Why This Error Happens

This error occurs when an ALTER SELECTIVE XML INDEX statement contains duplicate REMOVE clauses referencing the identical XML pathname within the same batch. SQL Server’s XML indexing engine validates that each path modification is unique during the parse phase, and it rejects the statement outright when it detects redundant removal instructions for a single path. This is typically caused by poorly generated or copy-pasted T-SQL scripts, automated schema migration tools duplicating path entries, or manual errors when consolidating multiple ALTER statements into one.

Step-by-Step Resolution Guide

Step 1: Review the Full ALTER Statement Syntax

  • Open the exact T-SQL script that triggered Error 9536 in SQL Server Management Studio (SSMS) or Azure Data Studio.
  • Locate every instance of the REMOVE keyword within the ALTER SELECTIVE XML INDEX statement.
  • Note the pathname (e.g., '/root/element/@attribute') listed after each REMOVE clause.

Step 2: Identify and Eliminate Duplicate Pathnames

  • Cross-reference each REMOVE '%.*ls' entry against the others in the same statement.
  • Delete the redundant line(s) so that each unique pathname appears only once in the REMOVE list.
  • If you intended to remove multiple distinct paths, ensure they are formatted as a comma-separated list rather than repeated identical entries.

Step 3: Validate the Current Index Path Definitions

  • Run the following query to inspect existing paths on the selective XML index before re-running the ALTER command:
SELECT path_id, path_expression, is_primitive_path 
FROM sys.selective_xml_index_paths 
WHERE xml_index_id = (SELECT index_id FROM sys.indexes WHERE name = 'YourXMLIndexName');
  • Confirm the pathname you intend to remove actually exists and matches the exact casing and syntax (including namespace prefixes) stored in the system view.

Step 4: Re-execute the Corrected ALTER Statement

  • Submit the cleaned-up script with a single REMOVE entry per unique path:
ALTER SELECTIVE XML INDEX YourIndexName ON YourSchema.YourTable
REMOVE ('/root/element/@attribute');
  • Monitor the Messages tab in SSMS for a successful “Command(s) completed successfully” confirmation instead of Error 9536.

Step 5: Check for Automated Script Generators

  • If the script was generated by a third-party ORM, migration tool, or deployment pipeline (e.g., Redgate, Liquibase, or custom PowerShell DacFx scripts), inspect the generator’s template logic.
  • Update the generator configuration to deduplicate path entries before the script reaches the SQL Server 2025 engine.

Alternative or Verification Steps

  • Verify the fix by querying sys.selective_xml_index_paths again after the ALTER statement completes to confirm the path was successfully removed and no duplicates remain in the index metadata.
  • If the error persists despite a visually clean script, drop and recreate the selective XML index entirely using DROP INDEX followed by a fresh CREATE SELECTIVE XML INDEX statement with the correct, de-duplicated path set.