Troubleshooting Guide: SQL Server Error 2273 – “Unterminated CDATA Section”

Why This Error Happens

Error 2273 occurs when SQL Server’s XML parser encounters a CDATA block (<![CDATA[ ... ]]>) that is missing its closing ]]> delimiter, causing the parser to reach the end of the document before the section is properly terminated. This is typically caused by malformed XML being passed into functions like sp_xml_preparedocument, FOR XML queries, or truncated data from an application layer that exceeds a VARCHAR/NVARCHAR column length. It can also occur when XML data is corrupted during transit, encoding conversion (UTF-8 to UTF-16), or when a BCP/bulk import job cuts off a large XML payload mid-stream.

Step-by-Step Resolution Guide

Step 1: Validate the Source XML Document

• Extract the raw XML string being passed to SQL Server and validate it using an external XML validator (e.g., Visual Studio XML Editor or xmllint).
• Specifically search for <![CDATA[ instances and confirm each one has a matching ]]> before the document ends.
• Check if the XML was truncated — count the character length of the variable against the column/variable definition (e.g., DECLARE @xmlData NVARCHAR(MAX) instead of a fixed length like NVARCHAR(4000)).

Step 2: Check Data Type and Variable Length Limits

• Open SSMS and review the stored procedure or application code handling the XML insert/update.
• Run the following to confirm no truncation is occurring during assignment:
• SELECT DATALENGTH(@xmlData) AS CurrentLength;
• Update any VARCHAR(n) or NVARCHAR(n) parameters handling XML to NVARCHAR(MAX) to prevent silent truncation of large CDATA payloads.
• If using sp_xml_preparedocument, ensure the document handle buffer isn’t exceeding memory limits (default 1/8 of SQL Server memory); adjust via sp_configure 'max server memory' if needed on high-load Windows Server 2025 instances.

Step 3: Inspect Encoding and Transport Layer

• If data is coming from a web service, API, or file import (SSIS, BCP), verify the encoding declaration (UTF-8 vs UTF-16) matches the actual byte stream.
• On Windows Server 2025, check the application pool or service account in IIS (if applicable) for connection timeout settings that might cut off large payloads mid-stream:
• Navigate to C:\Windows\System32\inetsrv\config\applicationHost.config
• Review <httpRuntime executionTimeout> and increase if XML payloads are large.
• Re-run the import/query after confirming the full XML payload arrives intact using SQL Profiler or Extended Events to capture the exact string received by SQL Server.

Step 4: Manually Repair or Escape the CDATA Block

• If the issue stems from manually constructed XML (via string concatenation in T-SQL or application code), ensure every opening <![CDATA[ has a corresponding closing ]]>.
• Use REPLACE() or regex validation in your application layer to auto-close any orphaned CDATA tags before sending to SQL Server:
• SET @xmlData = @xmlData + CASE WHEN @xmlData LIKE '%<![CDATA[%' AND @xmlData NOT LIKE '%]]>' THEN ']]>' ELSE '' END;

Alternative or Verification Steps

• Verify the fix: Re-execute the original query or stored procedure using SELECT CAST(@xmlData AS XML); — if it parses without throwing Error 2273, the CDATA section is now properly closed.
• Alternative solution: If the XML source is third-party and cannot be modified, pre-process the string in your application (C#, PowerShell, etc.) to strip or repair malformed CDATA sections before passing it to SQL Server, rather than relying on SQL Server’s native XML parser to handle malformed input.