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

by

Troubleshooting Guide: SQL Server Error 9442 – XML Parsing Error

Why This Error Happens

Error 9442 occurs when SQL Server’s XML parser encounters malformed, incomplete, or improperly encoded XML data at a specific line number within an XML data type column, variable, or document being processed. This is commonly caused by corrupted XML input (truncated tags, mismatched elements, or invalid characters), incorrect encoding declarations (e.g., UTF-16 data marked as UTF-8), or data truncation during transmission from an application or ETL process. In some cases, this surfaces after upgrading to SQL Server 2025 due to stricter XML schema validation introduced in the new query engine.

Step-by-Step Resolution Guide

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

  • Run the failing query in SQL Server Management Studio (SSMS) and note the exact line number reported in the error message.
  • Use SELECT CAST(YourXMLColumn AS NVARCHAR(MAX)) to extract the raw XML and inspect it manually for malformed tags or truncation.
  • Check if the XML is being generated by an external application (e.g., a .NET service or PowerShell script) — inspect that application’s serialization logic.

Step 2: Validate and Repair the XML Structure

  • Open the extracted XML in a validator (Visual Studio, Notepad++ with XML plugin, or xmllint on Windows Server 2025 via WSL).
  • Look for common issues: unclosed tags, invalid special characters (&, <, > not escaped), or an incorrect <?xml version="1.0" encoding="UTF-8"?> declaration that doesn’t match the actual byte encoding.
  • If the data is stored in a file, re-save it explicitly as UTF-8 without BOM using Notepad++ or VS Code, since mismatched BOM headers are a frequent cause of Error 9442 in SQL Server 2025.

Step 3: Correct the Application or Import Process

  • If data is imported via OPENROWSET(BULK...) or BULK INSERT, verify the SINGLE_CLOB or SINGLE_NCLOB option matches the actual file encoding.
  • For application-side XML generation, ensure the XML writer (e.g., System.Xml.XmlWriter in .NET 9) is not truncating output due to buffer size limits or premature stream closure.
  • Re-run the insert/update statement with the corrected XML payload.

Step 4: Apply SQL Server 2025 Compatibility Fixes

  • Check Windows Update and SQL Server Cumulative Updates (Help > About in SSMS) to confirm you’re on the latest CU, as early SQL Server 2025 builds had stricter XSD validation that flagged previously “tolerated” XML.
  • If necessary, temporarily lower the database compatibility level using:
    ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 160;
    to confirm whether the issue is tied to new parser behavior, then re-enable after fixing source XML.

Alternative or Verification Steps

  • Verify the fix by re-running the original INSERT, UPDATE, or SELECT statement that triggered Error 9442 — it should now execute without the XML parsing error.
  • As an alternative, use TRY_CAST or TRY_CONVERT instead of CAST/CONVERT to gracefully handle any remaining malformed XML rows without halting the entire batch, allowing you to isolate and log problem records for further cleanup.