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

by

Troubleshooting Guide: SQL Server Error 2213 — “Cannot atomize/apply data() on expression”

Why This Error Happens

Error 2213 typically occurs when SQL Server’s XML/XQuery engine attempts to apply the data() function or atomize a sequence that contains an incompatible or unexpected inferred type, such as a complex element node or an xs:anyType that cannot be safely cast. This usually stems from malformed XML schema collections, corrupted query plans involving XML indexes, or a mismatch between the XQuery expression and the underlying XML schema definition. It can also surface after an in-place upgrade to SQL Server 2025 where cached execution plans reference deprecated type inference behavior.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and XML Schema Collection

  • Capture the exact query via SQL Server Profiler or Extended Events (sqlserver.error_reported filtered on Error 2213).
  • Run SELECT * FROM sys.xml_schema_collections; to locate the schema collection referenced in the failing query.
  • Check if the XQuery expression uses .value(), .query(), or .nodes() on a node that resolves to a complex type instead of a simple/atomic type.

Step 2: Clear and Rebuild Cached Execution Plans

  • Open an elevated PowerShell or SQL Server Management Studio (SSMS) session on the Windows Server 2025 host.
  • Execute DBCC FREEPROCCACHE; to remove potentially corrupted cached plans tied to the XML type inference.
  • Follow with DBCC DROPCLEANBUFFERS; in a non-production environment to force fresh plan compilation.
  • Re-run the query to confirm whether the cached plan was the root cause.

Step 3: Validate and Repair the XML Schema Definition

  • Script out the XML schema collection using sp_help_xml_schema_collection or extract via SSMS Object Explorer.
  • Confirm the schema’s xs:complexType and xs:simpleType definitions align with how the query atomizes data (e.g., ensure data() is only applied to leaf nodes with simple types).
  • If corruption is suspected, drop and recreate the schema collection:
  • DROP XML SCHEMA COLLECTION [SchemaName];
  • CREATE XML SCHEMA COLLECTION [SchemaName] AS N'<schema XML content>';

Step 4: Apply Latest Cumulative Update

  • Navigate to C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\ (adjust version path for SQL Server 2025 builds) to check current patch level via setup.exe /q /ACTION=Upgrade.
  • Download and install the latest Cumulative Update (CU) from the Microsoft Update Catalog, as this error has known fixes tied to XQuery type-inference regressions in early 2025 builds.
  • Restart the SQL Server service via services.msc or Restart-Service -Name 'MSSQLSERVER' in PowerShell after patching.

Step 5: Rewrite the XQuery Expression to Avoid Ambiguous Atomization

  • Replace direct data() calls on potentially complex nodes with explicit .value('(node)[1]', 'datatype') syntax to force correct type casting.
  • Test the rewritten query in isolation using SET SHOWPLAN_XML ON; to confirm the execution plan no longer throws Error 2213.

Alternative or Verification Steps

  • Run the corrected query under SET STATISTICS XML ON; and confirm no error is thrown while validating the returned atomic values match expected output.
  • If the issue persists after schema repair and patching, restore the affected database from a pre-upgrade backup and re-apply only validated XML schema collections before retrying the migration to SQL Server 2025.