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

by

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

Why This Error Happens

Error 9446 occurs when SQL Server’s XML parser encounters malformed, truncated, or incorrectly encoded XML data while attempting to parse a value into the xml data type. This is commonly caused by invalid characters embedded in the XML document, mismatched or unclosed tags, or data truncation that occurs when XML content exceeds buffer limits during insertion or import. It can also arise from corrupted data pages, improperly escaped special characters (such as &, <, >), or encoding mismatches (e.g., UTF-8 vs. UTF-16) between the source application and SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the XML Error

  • Run the failing query with SET SHOWPLAN_XML OFF and capture the full error message, noting the specific line number referenced in the error.
  • Use SELECT CAST(YourXmlColumn AS NVARCHAR(MAX)) on the suspect row to visually inspect the raw XML content for malformed tags or special characters.
  • Check SQL Server Error Logs located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional context around the failure timestamp.

Step 2: Validate and Sanitize the XML Input

  • Open the source XML file or string in a validator (e.g., Visual Studio Code with an XML extension, or xmllint.exe from the Windows 11 / Windows Server 2025 developer tools) to confirm well-formed structure.
  • Ensure special characters are properly escaped: replace raw & with &amp;, < with &lt;, and > with &gt; in the source data.
  • If importing via SSIS or BCP, verify the CodePage and DataFileType settings match the source encoding (UTF-8 vs UTF-16) to prevent truncation mid-parse.

Step 3: Check for Data Truncation in Transit

  • Inspect the source application or ETL pipeline for NVARCHAR(MAX) vs NVARCHAR(n) mismatches that could silently truncate long XML strings before they reach SQL Server.
  • If using a .NET or ODBC driver connection string, confirm Packet Size is set sufficiently high (e.g., Packet Size=32767) in ODBC Data Source Administrator on Windows Server 2025.
  • Re-run the insert/import operation with a known-good, small XML sample to isolate whether the issue is data-specific or configuration-specific.

Step 4: Rebuild or Repair Corrupted XML Indexes

  • If the error persists only on existing stored XML data (not new inserts), run DBCC CHECKTABLE('YourTableName') WITH ALL_ERRORMSGS to check for underlying corruption.
  • Drop and rebuild any primary or secondary XML indexes using DROP INDEX followed by CREATE PRIMARY XML INDEX to eliminate index-level corruption as the cause.

Alternative or Verification Steps

  • Verify the fix by re-running the original failing query or import job and confirming Error 9446 no longer appears in ERRORLOG or SQL Server Management Studio’s Messages pane.
  • As an alternative, stage the XML data as NVARCHAR(MAX) first, run a validation check using YourColumn.exist('/') to confirm well-formedness, and only then cast it to the xml data type during final insertion.