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

by

Troubleshooting Guide: SQL Server Error 2240 — “The target of ‘insert into’ must be an element/document node”

Why This Error Happens

This error occurs when an XML DML insert into statement targets a node that is not a valid element or document node, such as an attribute, text, or comment node. SQL Server’s XML data type methods (specifically .modify()) strictly enforce the XQuery/XML DML schema rules, which require the insertion target to have child-node capability. This typically results from an incorrect XPath expression in the .modify('insert ... into (...)') clause, a malformed XML schema, or a corrupted XML column structure that no longer conforms to the expected node hierarchy.

Step-by-Step Resolution Guide

Step 1: Review and Correct the XPath Target in the .modify() Statement

  • Open the T-SQL script containing the insert into XML DML command causing Error 2240.
  • Verify the XPath expression inside the into (...) clause points to an element or document node, not an attribute (@attribute), text node, or comment node.
  • Example of a correct syntax:
  UPDATE MyTable
  SET XmlColumn.modify('insert <NewNode/> into (/Root/Element)[1]')
  WHERE Id = 1;
  • Ensure you are not mistakenly targeting /Root/Element/@attr or text() nodes.

Step 2: Validate the XML Schema Collection (if applicable)

  • If the column uses a typed XML schema (XML (XmlSchemaCollection)), run the following to confirm the schema allows the intended node structure:
  SELECT xsc.name, xsc.xml_collection_id
  FROM sys.xml_schema_collections xsc
  WHERE xsc.name = 'YourSchemaCollectionName';
  • Confirm the schema permits child elements at the target insertion point; untyped XML columns bypass this check but typed columns will throw Error 2240 if the schema structure disallows it.
  • Re-import or update the schema collection using CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION if it is outdated or corrupted.

Step 3: Test the XML Data Integrity

  • Extract the current XML value from the problematic row to inspect its actual structure:
  SELECT XmlColumn FROM MyTable WHERE Id = 1;
  • Check for malformed or unexpected node types (e.g., stray comments, processing instructions, or empty document nodes) that may be interfering with the insert target path.
  • If corruption is found, rebuild the XML value manually or restore it from a known-good backup using RESTORE DATABASE ... WITH PARTIAL or a table-level data copy from a backup instance.

Step 4: Apply Latest Cumulative Update for SQL Server 2025

  • On Windows Server 2025 or Windows 11, open SQL Server Installation Center or check via:
  SELECT @@VERSION;
  • Compare the build number against the latest Cumulative Update (CU) listed on the Microsoft Update Catalog for SQL Server 2025.
  • Download and install the latest CU from https://www.microsoft.com/download/ if your instance is behind, as XML DML parser fixes are occasionally included in servicing updates.

Alternative or Verification Steps

  • After applying the fix, re-run the original .modify('insert ... into (...)') statement and confirm it completes without Error 2240; also run SELECT XmlColumn FROM MyTable WHERE Id = 1 to visually confirm the new node was inserted correctly.
  • As an alternative, if the target node type cannot be changed, use insert ... as first/last into syntax with a corrected parent element reference, or cast the XML to untyped (CONVERT(XML, XmlColumn)) temporarily to isolate whether the schema collection is the root cause.