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

by

Troubleshooting Guide: SQL Server Error 9466 — XML Parsing Error

Why This Error Happens

Error 9466 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data at a specific line and character position within a column, variable, or input file. This is typically caused by truncated XML documents (often from interrupted data transfers or improperly concatenated strings exceeding NVARCHAR(MAX) limits), mismatched or unescaped special characters (such as &, <, or > not converted to entities), or an incorrect encoding declaration (e.g., a file declared as UTF-16 but saved as UTF-8). In rare cases, this can also stem from a corrupted source file or a network interruption during a bulk insert operation that cut off the XML payload mid-stream.

Step-by-Step Resolution Guide

Step 1: Isolate and Inspect the Malformed XML Source

  • Locate the exact line number referenced in the error (e.g., “line 42”) within the source file or variable.
  • If importing via OPENROWSET(BULK...), open the source .xml file in a text editor (like VS Code or Notepad++) and navigate to that specific line.
  • Check for unescaped ampersands (&), unclosed tags, or abrupt file termination (missing closing root tag).

Step 2: Validate Character Encoding and Special Characters

  • Verify the file’s encoding matches the declared XML header (e.g., <?xml version="1.0" encoding="UTF-8"?>).
  • In Windows 11/Server 2025, use PowerShell to check the Byte Order Mark (BOM):
 Get-Content -Path "C:\Data\source.xml" -TotalCount 1 -Encoding Byte
  • Replace any raw special characters with proper XML entities (e.g., replace & with &amp;).

Step 3: Verify Data Type Length and Truncation

  • If generating XML via a FOR XML query or concatenating strings in T-SQL, ensure the receiving variable is cast as NVARCHAR(MAX) rather than a fixed length (like NVARCHAR(4000)), which can silently truncate the XML and break the closing tags.
  • Run SELECT DATALENGTH(YourXmlColumn) to confirm the data wasn’t cut off during a VARCHAR conversion.

Step 4: Test the Repaired XML Snippet in Isolation

  • Copy just the problematic line segment into a new query window.
  • Attempt to cast it directly:
 DECLARE @test XML;
 SET @test = '<root><node>Test &amp; Data</node></root>';
 SELECT @test;
  • If this isolated test succeeds, the issue is confirmed to be specific to the original file’s content, not a SQL Server configuration setting.

Alternative or Verification Steps

  • Verification: Re-run the original query or import job; if successful, query the resulting XML column using .nodes() or .value() to ensure the structure is fully queryable and not just superficially valid.
  • Alternative: If the source file is generated by a third-party application and cannot be easily edited, use an XSD schema validation step via sp_xml_schema or a pre-processing PowerShell script to sanitize/validate the file before it reaches the INSERT or OPENROWSET statement.