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

by

Troubleshooting Guide: SQL Server Error 6391 – Invalid Key Column Type in Secondary Selective XML Index

Why This Error Happens

This error occurs when SQL Server attempts to promote a node identified by an XML path expression to a SQL data type that is not supported as a key column in a secondary selective XML index (SXI). Secondary selective XML indexes only support a specific subset of scalar SQL types (such as INT, BIGINT, NVARCHAR, DATETIME2, etc.) for promoted paths, and the node being referenced either resolves to an unsupported type (like XML, SQL_VARIANT, or a CLR type) or returns a value that is ambiguous in type due to mixed-content XML nodes. This typically stems from a mismatch between the PROMOTE type specified in the CREATE SELECTIVE XML INDEX or CREATE XML INDEX ... FOR PATH statement and the actual data stored at that XML path.

Step-by-Step Resolution Guide

Step 1: Review the XML Index Definition

  • Locate the CREATE SELECTIVE XML INDEX or the associated sys.xml_index_nodes / sys.selective_xml_index_paths metadata for the affected index.
  • Run the following query to inspect promoted path types:
SELECT path, [type], is_wildcard 
FROM sys.selective_xml_index_paths 
WHERE xml_index_id = (SELECT index_id FROM sys.indexes WHERE name = 'YourXMLIndexName');
  • Identify the specific path mentioned in the Error 6391 message (the %.*ls placeholder will show the actual path in the real error output).

Step 2: Correct the Promoted Data Type

  • Edit the index creation script and change the PROMOTE clause to a supported scalar type (e.g., NVARCHAR(100), INT, BIGINT, DATETIME2, DECIMAL, FLOAT, BIT, or UNIQUEIDENTIFIER).
  • Avoid promoting paths to XML, SQL_VARIANT, VARBINARY(MAX), or any CLR-based user-defined type, as these are explicitly disallowed in secondary selective XML indexes.
  • Example corrected syntax:
CREATE SELECTIVE XML INDEX SXI_Orders ON dbo.OrdersTable(XmlColumn)
FOR (
    PathOrderID = '/Order/@ID' AS SQL BIGINT,
    PathOrderDate = '/Order/Date' AS SQL DATETIME2
);

Step 3: Validate the Underlying XML Data

  • Query sample XML data to confirm the actual node content matches the expected promoted type:
SELECT XmlColumn.value('(/Order/Date)[1]', 'DATETIME2')
FROM dbo.OrdersTable;
  • If the XML contains mixed-type or inconsistent values at that path (e.g., some nodes are dates, others are plain text), normalize the source XML data or exclude that path from promotion.

Step 4: Drop and Recreate the Secondary XML Index

  • Since index definitions cannot be altered in place, drop the existing secondary index:
DROP INDEX SXI_Orders ON dbo.OrdersTable;
  • Recreate it using the corrected path/type mapping from Step 2.
  • Ensure the primary XML index already exists before creating the secondary selective index, as SQL Server 2025 still enforces this dependency.

Step 5: Apply Latest Cumulative Update

  • Check Windows Server 2025 / Windows 11 for the latest SQL Server 2025 Cumulative Update (CU) via SQL Server Setup or Microsoft Update Catalog.
  • Some early builds of SQL Server 2025 contain known type-promotion bugs with selective XML indexes on nested or namespace-qualified paths; applying the latest CU resolves several of these edge cases.

Alternative or Verification Steps

  • After recreating the index, run DBCC CHECKDB and re-execute a test query using the promoted path in a WHERE clause to confirm the optimizer utilizes the new secondary XML index without throwing Error 6391.
  • If the issue persists, as a workaround, remove the problematic path from the selective index definition entirely and rely on a standard primary XML index with runtime .value()/.nodes() queries until the data type inconsistency in the source XML is resolved.