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

by

Why This Error Happens

Error 2226 occurs when executing an XML DML insert statement against an xml data type column where the target path expression (XQuery) resolves to multiple nodes instead of exactly one. SQL Server’s .modify() method strictly requires a single insertion point, so the engine throws this error when the underlying XML schema contains repeating elements that match the specified XPath. This is typically a query-authoring issue rather than a server configuration fault, often triggered after a schema change, a bad migration script, or an XML document that grew additional sibling nodes.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Capture the exact T-SQL statement using Extended Events or the SQL Server 2025 Query Store (sys.query_store_query_text).
  • Isolate the .modify('insert ... into (/path)') clause causing the failure.
  • Run the inner XQuery path alone using SELECT col.query('/path') to see how many nodes are returned.

Step 2: Refine the XPath to Target a Single Node

  • Add a positional predicate to force uniqueness, e.g., change /Root/Item to /Root/Item[1].
  • Alternatively, use a unique attribute filter: /Root/Item[@id="123"].
  • Validate the corrected path returns exactly one node:
SELECT XmlColumn.query('/Root/Item[1]') 
FROM YourTable;

Step 3: Validate the XML Schema Collection (If Applicable)

  • If the column is bound to an XML SCHEMA COLLECTION, check whether a recent schema update (ALTER XML SCHEMA COLLECTION) introduced a repeating element (maxOccurs="unbounded") where a single element was previously expected.
  • Run sp_help or query sys.xml_schema_collections to confirm current constraints.
  • Update the schema or adjust the insert logic to explicitly loop through nodes using .nodes() cross apply if multiple insertions are actually intended.

Step 4: Rewrite Using .nodes() for Bulk Inserts (If Multiple Targets Are Valid)

  • If the business logic genuinely requires inserting into multiple nodes, do not force a single-node XPath — instead, iterate:
UPDATE YourTable
SET XmlColumn.modify('insert <NewNode/> as last into (/Root/Item)[1]')
  • Use CROSS APPLY XmlColumn.nodes('/Root/Item') AS T(c) combined with a loop or batch update construct for each node individually to avoid ambiguity.

Alternative or Verification Steps

  • Re-run the original failing stored procedure or application transaction in SQL Server Management Studio (SSMS) 21 against Windows Server 2025 — confirm Error 2226 no longer appears and the row count/XML output matches expectations.
  • If the error persists, dump the XML column value to a staging table using SELECT CAST(XmlColumn AS NVARCHAR(MAX)) to visually inspect the document structure for unexpected duplicate nodes before re-testing the corrected XPath.