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

by

Troubleshooting Guide: SQL Server Error 2389 — “Requires a Singleton (or Empty Sequence)”

Why This Error Happens

Error 2389 occurs when an XQuery or XPath expression executed against an xml data type column returns multiple nodes, but the calling function (such as value()) expects a single scalar result or an empty sequence. This typically happens due to poorly constrained XPath predicates, malformed XML schema collections, or queries written against XML data that has grown to include unexpected repeating elements. It is a logical/query design issue rather than a corruption or permissions issue, but it can also surface after an in-place upgrade to SQL Server 2025 if legacy XQuery syntax is no longer being evaluated the same way against updated XML indexes.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Capture the exact T-SQL statement from the application trace, SQL Server error log, or Extended Events session.
  • Look specifically for .value(), .exist(), or .query() method calls on an xml typed column or variable.
  • Example of a problematic pattern:
  SELECT XmlCol.value('(/Root/Item)[1]/text()[1]', 'nvarchar(100)')

Step 2: Correct the XPath to Enforce a Singleton

  • Add explicit positional predicates to force a single-node result instead of a node-set.
  • Rewrite the query using array indexing syntax, for example:
  SELECT XmlCol.value('(/Root/Item)[1]', 'nvarchar(100)')

instead of:

  SELECT XmlCol.value('/Root/Item', 'nvarchar(100)')
  • If multiple nodes are expected, switch to .nodes() combined with CROSS APPLY to shred results into rows rather than forcing a scalar extraction.

Step 3: Validate the Underlying XML Schema Collection

  • Open SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) and run:
  SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaName';
  • Confirm the schema does not allow unbounded repeating elements (maxOccurs="unbounded") where your application logic assumes a single occurrence.
  • If necessary, update the XML Schema Collection definition using ALTER XML SCHEMA COLLECTION to tighten the maxOccurs constraint.

Step 4: Rebuild XML Indexes (If Applicable)

  • Navigate to the affected table in SSMS under Databases > [YourDB] > Tables > [YourTable] > Indexes.
  • Right-click the Primary XML Index and select Rebuild to ensure the index reflects current document structure after schema or data changes:
  ALTER INDEX ALL ON dbo.YourTable REBUILD;
  • This ensures stale index statistics aren’t contributing to inconsistent query evaluation under the new SQL Server 2025 query optimizer.

Alternative or Verification Steps

  • Re-run the corrected query in a test environment using SET SHOWPLAN_XML ON to confirm the execution plan no longer throws Error 2389 and returns a single scalar value.
  • If the error persists intermittently, use .query() first to inspect the raw node-set being returned (SELECT XmlCol.query('/Root/Item')) to visually confirm how many nodes match your XPath before finalizing the .value() expression.