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

by

Troubleshooting Guide: SQL Server Error 9424 – XML Parsing Error

Why This Error Happens

Error 9424 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or non-well-formed XML data at a specific line number during a parsing operation. This is typically caused by invalid XML syntax (unclosed tags, mismatched elements, illegal characters, or encoding mismatches) being passed into functions like OPENXML, sys.xml, TRY_CONVERT, or during XML data type column operations. It can also surface due to truncated data transfers, corrupted source files, or application code that incorrectly constructs XML strings before sending them to SQL Server 2025.

Step-by-Step Resolution Guide

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

  • Note the specific line number reported in the error message (e.g., “XML parsing: line 12”).
  • Query the source table or variable holding the XML data using SELECT CAST(YourXMLColumn AS NVARCHAR(MAX)) to export the raw string for inspection.
  • Use a tool like Notepad++, VS Code, or an online XML validator to pinpoint the syntax issue at that line.

Step 2: Validate and Correct the XML Structure

  • Check for common issues: unclosed tags (<tag> without </tag>), unescaped special characters (&, <, > not converted to &amp;, &lt;, &gt;), or mismatched namespaces.
  • Verify the encoding declaration at the top of the XML (e.g., <?xml version="1.0" encoding="UTF-8"?>) matches the actual byte encoding of the file or string.
  • If the XML originates from an external API or application, review the serialization logic in the source code (e.g., .NET XmlWriter settings) to ensure proper escaping.

Step 3: Test the Corrected XML in a Controlled Environment

  • Run the corrected XML through a simple test query on Windows Server 2025:
DECLARE @x XML = N'<YourCorrectedXML>Data</YourCorrectedXML>';
SELECT @x;
  • If using OPENXML, ensure you are also calling sp_xml_removedocument after processing to release memory and avoid downstream parsing conflicts.
  • Confirm that SQL Server 2025 instance collation settings support the character set used in the XML payload (check via SELECT SERVERPROPERTY('Collation')).

Step 4: Check for File Corruption or Transmission Truncation

  • If the XML is read from a file path (e.g., via OPENROWSET(BULK...)), verify the file isn’t truncated by checking the file size against the expected output size from the source system.
  • Re-transfer or re-export the file to rule out network transmission corruption, especially if moving files between Windows Server 2025 shares or legacy systems.

Alternative or Verification Steps

  • Verify the Fix: Re-run the original stored procedure or query that triggered Error 9424; successful execution without the error confirms the XML structure is now valid.
  • Alternative Solution: If the XML source cannot be modified immediately, wrap the parsing logic in a TRY...CATCH block to gracefully log the malformed record ID (rather than crashing the batch) and flag it for manual data cleansing.