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

by

Troubleshooting Guide: SQL Server Error 9401 — “XML Parsing: Line %d”

Why This Error Happens

Error 9401 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data being inserted into an XML-typed column or processed via functions like OPENXML, sys.xml, or TRY_CONVERT. This is typically caused by invalid tags, mismatched namespaces, unescaped special characters, or truncated XML content delivered by an upstream application. In some cases on Windows Server 2025 / Windows 11, corrupted MSXML libraries or a mismatched collation/encoding setting can also cause valid XML to be misread.

Step-by-Step Resolution Guide

Step 1: Identify and Validate the Offending XML Document

  • Locate the exact line/column referenced in the error message (line %d) within the source XML payload.
  • Extract the XML string using SELECT CAST(YourXmlColumn AS NVARCHAR(MAX)) to inspect it manually.
  • Run the extracted XML through a validator (e.g., Visual Studio XML Editor, xmllint, or an online validator) to pinpoint syntax errors like unclosed tags or invalid characters.

Step 2: Check Encoding and Escaping Issues

  • Confirm the XML declaration matches the actual encoding (e.g., <?xml version="1.0" encoding="UTF-8"?> vs. actual UTF-16 BOM).
  • Ensure special characters (&, <, >) in text nodes are properly escaped as &amp;, &lt;, &gt;.
  • If XML is generated by an external application, review the client-side serialization logic (e.g., .NET XmlWriter settings) for incorrect encoding configuration.

Step 3: Repair or Sanitize the Source Data

  • If the XML is stored in a staging table, use REPLACE() or TRANSLATE() functions in T-SQL to strip invalid characters before casting to the XML data type.
  • For recurring malformed payloads, implement a pre-validation step in the application layer using XmlDocument.LoadXml() (C#) or lxml.etree.fromstring() (Python) to catch errors before they reach SQL Server.
  • If using OPENROWSET(BULK...) to import an XML file, verify the file path and confirm the file is not truncated: SELECT * FROM OPENROWSET(BULK 'C:\Data\file.xml', SINGLE_BLOB) AS x;

Step 4: Update SQL Server and MSXML Components

  • On Windows Server 2025, check for the latest Cumulative Update (CU) for SQL Server 2025 via SQL Server Setup or Microsoft Update Catalog, as XML parser bug fixes are periodically shipped in CUs.
  • Verify the MSXML6.dll version is current by checking C:\Windows\System32\msxml6.dll properties (Windows 11 and Server 2025 ship MSXML6 by default; reinstalling via sfc /scannow can repair corruption).

Alternative or Verification Steps

  • Re-run the original INSERT/CAST statement that triggered Error 9401 after sanitizing the XML; a successful execution with no error confirms the fix.
  • As an alternative, use TRY_CONVERT(XML, @YourData) instead of CAST/CONVERT to gracefully return NULL on malformed XML, allowing you to log and isolate bad records without halting the entire batch operation.