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

by

Troubleshooting Guide: SQL Server Error 2291 — “No root element was found”

Why This Error Happens

Error 2291 occurs when SQL Server attempts to parse an XML input (such as a configuration file, SSIS package, XML data type column, or an sp_xml_preparedocument call) that is either completely empty or does not contain valid XML content. This typically results from a corrupted or truncated file, a source file that was saved with zero bytes, or an application passing a NULL/empty string where well-formed XML is expected. It can also surface during SQL Server 2025 setup or service startup if an XML-based configuration file (like a Policy-Based Management policy or an SSIS package .dtsx file) has been damaged or incorrectly encoded (e.g., saved without proper BOM/encoding headers).

Step-by-Step Resolution Guide

Step 1: Identify the Source of the XML Input

  • Check the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to determine which process or query triggered the error.
  • If the error occurs during a job or SSIS package execution, locate the exact .dtsx file or XML source path referenced in the job step.
  • Run SELECT * FROM sys.dm_exec_requests WHERE session_id = <SPID> to identify the query context if the error is transactional.

Step 2: Validate the XML File or Data Integrity

  • Open the suspected XML file (configuration file, package, or data export) in a text editor like VS Code or Notepad++.
  • Confirm the file is not 0 KB in size — a root-element error almost always means the file is empty or truncated.
  • Check for a valid declaration at the top of the file, such as <?xml version="1.0" encoding="utf-8"?>, followed by an actual root node (e.g., <Configuration> or <DTS:Executable>).
  • If using OPENROWSET(BULK...) or sp_xml_preparedocument, verify the file path and encoding (UTF-8 vs UTF-16) match what SQL Server expects.

Step 3: Restore or Regenerate the Corrupted File

  • If the file is empty or damaged, restore it from the most recent backup location (e.g., D:\SSIS_Packages\Backup\).
  • For SSIS packages, redeploy the .dtsx file from source control or re-export it from SQL Server Data Tools (SSDT).
  • If the issue involves a Policy-Based Management (PBM) policy, re-import the policy via SSMS: Object Explorer → Management → Policy Management → Import Policy.

Step 4: Check Application/Driver-Level XML Submission

  • If a custom application or ETL tool is generating the XML dynamically, add a validation check (e.g., try/catch with XmlDocument.Load()) before passing data to SQL Server to catch empty strings earlier in the pipeline.
  • Ensure any API or middleware (such as a REST service feeding SQL Server) is not sending an empty HTTP response body that gets mistakenly parsed as XML.

Alternative or Verification Steps

  • Re-run the failing job, query, or SSIS package and confirm Error 2291 no longer appears in the SQL Server Agent job history or ERRORLOG.
  • As a quick alternative test, manually run EXEC sp_xml_preparedocument @handle OUTPUT, N'<Root><Test>1</Test></Root>' to confirm the XML parser itself is functioning correctly, isolating the issue to the specific file/data source rather than the SQL Server engine.