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

by

Troubleshooting Guide: SQL Server Error 9426 — XML Parsing Error

Why This Error Happens

Error 9426 occurs when SQL Server’s native XML parser encounters malformed, incomplete, or non-well-formed XML data at a specific line/character position during a parsing operation (such as OPENXML, .query(), .value(), or casting a string to the XML data type). This is typically caused by invalid XML syntax (unclosed tags, mismatched elements, illegal characters, or encoding mismatches) in the source data rather than a SQL Server configuration problem. It can also surface when the XML data was corrupted in transit, truncated by an upstream application, or when the character encoding declared in the XML prolog doesn’t match the actual byte encoding of the data.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Character Position

  • Note the exact line number and character offset reported in the error message (e.g., “XML parsing: line 12, character 45”).
  • Extract the raw XML payload causing the failure by querying the source table/column directly, or capture it via a SQL Server Profiler/Extended Events trace filtered on Error 9426.
  • Open the extracted XML in a text editor (Notepad++, VS Code) with line numbers enabled to inspect the exact location.

Step 2: Validate and Correct the XML Structure

  • Run the extracted XML through an XML validator (e.g., xmllint --noout file.xml on Windows via WSL, or an online/offline XML schema validator) to pinpoint well-formedness errors.
  • Check common culprits: unescaped special characters (&, <, > not converted to &amp;, &lt;, &gt;), mismatched opening/closing tags, and missing root elements.
  • Verify the encoding declaration in the XML prolog (e.g., <?xml version="1.0" encoding="UTF-8"?>) matches the actual file/stream encoding — mismatches between UTF-8 and UTF-16 are a frequent cause.
  • If the XML originates from an application (API, ETL job, or client app), check the generating code for improper string concatenation instead of using an XML serializer library.

Step 3: Correct the Source Data or Application Logic

  • If the malformed XML is already stored in a table, update the offending row using UPDATE table SET xmlcol = '<corrected>...</corrected>' WHERE id = X.
  • If the XML is generated dynamically, modify the application or stored procedure to use FOR XML PATH with proper escaping, or switch to using parameterized XML serialization methods instead of manual string building.
  • Re-run the failing query or import/ETL job (e.g., SSIS package, BULK INSERT, or OPENROWSET) against the corrected XML payload.

Step 4: Patch SQL Server and Review Collation/Encoding Settings

  • Confirm SQL Server 2025 is on the latest Cumulative Update via SELECT @@VERSION; and compare against Microsoft’s release notes, as XML parser edge-case bugs are occasionally patched.
  • Check the database collation (SELECT DATABASEPROPERTYEX('YourDB', 'Collation');) to ensure it supports the character set used in the incoming XML, especially for multi-byte or non-Latin character sets.

Alternative or Verification Steps

  • Re-execute the original query/import process and confirm Error 9426 no longer appears; additionally run SELECT CAST(xmlcol AS XML) on the corrected row to confirm it casts without error.
  • As an alternative, if the data source cannot be fixed immediately, wrap the parsing logic in a TRY...CATCH block combined with TRY_CAST/TRY_CONVERT to gracefully skip or log malformed rows instead of failing the entire batch.