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

by

Troubleshooting Guide: SQL Server Error 2356 — “The target of ‘replace value of’ must be a non-metadata attribute or an element with simple typed content”

Why This Error Happens

This error occurs when SQL Server’s XML processing engine attempts to execute an XQuery replace value of expression against a target node that does not qualify as a simple-typed element or attribute. In Microsoft SQL Server 2025, this typically happens when the XQuery path resolves to an element containing complex content (such as child elements or mixed content) rather than a single text/attribute node. It can also surface due to schema drift in XML indexes, corrupted XML column metadata, or a mismatch between the XML schema collection bound to the column and the actual document structure being modified.

Step-by-Step Resolution Guide

Step 1: Validate the XQuery Target Node Structure

  • Run a SELECT query using .query() on the same XPath used in your .modify('replace value of ...') statement to confirm the node is a leaf node.
  • Confirm the target does not contain nested child elements — replace value of only works on simple-typed content.
  • Example check:
  SELECT XmlColumn.query('/Root/Element[1]') FROM YourTable;

Step 2: Inspect and Repair XML Schema Collection Bindings

  • Navigate to SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) and expand Databases > [YourDB] > Programmability > Types > XML Schema Collections.
  • Run the following to check for schema mismatches:
  SELECT name, xml_collection_id FROM sys.xml_schema_collections;
  • If the schema collection is outdated or corrupted, drop and re-bind it using ALTER XML SCHEMA COLLECTION or re-import the .xsd file via T-SQL.

Step 3: Rebuild Affected XML Indexes

  • Open an elevated PowerShell or SQLCMD session on your Windows Server 2025 host.
  • Rebuild primary and secondary XML indexes to clear stale metadata pointers:
  ALTER INDEX ALL ON YourTable REBUILD;
  • Confirm there are no orphaned index fragments by checking sys.xml_indexes.

Step 4: Correct the .modify() Syntax to Target a Single Node

  • Rewrite the XQuery to explicitly target an atomic-valued node using [1] positional indexing to avoid ambiguous node sets.
  • Example fix:
  UPDATE YourTable
  SET XmlColumn.modify('replace value of (/Root/Element[1]/text())[1] with "NewValue"')
  WHERE Id = 1;
  • Ensure the path does not accidentally resolve to an element with sub-elements.

Step 5: Clear SQL Server Plan Cache and Restart the Service (If Needed)

  • If the error persists due to cached execution plans referencing old schema metadata, clear the plan cache:
  DBCC FREEPROCCACHE;
  • Restart the SQL Server service via Services.msc or Restart-Service MSSQLSERVER in PowerShell on Windows Server 2025.

Alternative or Verification Steps

  • Re-run the original UPDATE ... .modify('replace value of ...') statement and confirm it completes without Error 2356 in the SQL Server Error Log (Event Viewer > Applications and Services Logs > MSSQLSERVER).
  • If the issue persists, use .modify('replace value of (expression)[1] with (...)') with an explicit text() node cast, or fall back to .modify('delete ...') followed by .modify('insert ...') to fully replace the element structure.