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

by

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

Why This Error Happens

Error 9438 occurs when SQL Server’s XML parser encounters malformed, incomplete, or improperly encoded XML data being inserted into or processed through an xml data type column. This is typically caused by truncated XML strings, mismatched or unclosed tags, invalid character encoding (such as non-UTF-8 characters in a UTF-8 expected stream), or corruption introduced during data transfer from an external application, ETL process, or client driver. It can also surface when application code concatenates XML fragments incorrectly before passing 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 (%d) referenced in the error message.
  • Run the failing INSERT, UPDATE, or CAST/CONVERT statement in isolation using SQL Server Management Studio (SSMS) to capture the raw XML payload.
  • Use SELECT LEN(@xmlVariable) and SELECT @xmlVariable to inspect the string length and content for truncation or unexpected characters before casting to xml.

Step 2: Validate and Repair the XML Structure

  • Check for unclosed tags, mismatched namespaces, or missing closing brackets near the reported line number.
  • Verify the encoding declaration (e.g., <?xml version="1.0" encoding="UTF-8"?>) matches the actual byte encoding of the source file or stream.
  • If the XML originates from an external file, open it in a validator (e.g., Visual Studio Code with an XML extension, or xmllint.exe on Windows Server 2025) to confirm well-formedness before re-importing.
  • On Windows 11/Server 2025, use PowerShell to quickly validate: [xml](Get-Content "C:\Data\input.xml").

Step 3: Correct the Application or Import Process

  • If data is coming from an ETL pipeline (SSIS, Azure Data Factory, or custom .NET code), ensure the XML fragment is fully assembled (no mid-stream truncation) before being passed to the xml parameter.
  • Check connection string and driver settings (ODBC/OLE DB) on the Windows client for correct text encoding (Use Encryption for Data = No conflicts or MARS settings can sometimes truncate large XML payloads).
  • Increase MAX XML DOCUMENT SIZE limits if applicable, and confirm the column/parameter isn’t being truncated by an upstream varchar(n) cast with insufficient length.

Step 4: Re-test with a Minimal Reproduction

  • Isolate a small, known-good XML snippet and incrementally add complexity to pinpoint where the parser breaks.
  • Use SELECT CAST('<root><child>test</child></root>' AS xml) as a baseline sanity check in SSMS on SQL Server 2025.

Alternative or Verification Steps

  • Confirm the fix by re-running the original query/import job and verifying it completes without raising Error 9438, then spot-check the inserted XML with SELECT CAST(xmlColumn AS nvarchar(max)).
  • If the issue persists and the source system cannot guarantee well-formed XML, wrap the cast in a TRY_CAST or TRY_CONVERT to gracefully handle malformed entries and log them separately for manual review instead of failing the entire batch.