Troubleshooting “XML parsing error” when importing SQL XML data types.

When importing records containing SQL Server XML columns into Excel, data refreshes frequently halt with the error: DataFormat.Error: An XML parsing error occurred. In other cases, the query completes, but the column displays nested [Table] or [Record] objects that cause errors when expanded, or the sheet fails to load entirely due to XML validation breaks.

In SQL Server, the XML data type stores structured or semi-structured markup based on strict W3C standards. When Excel imports these records via Power Query, OLE DB, or ODBC, parsing failures occur because of unescaped entity references (&, <, >), non-printable ASCII characters within the markup, undeclared XML namespaces, or “XML fragments” that lack a single enclosing root element.

Quick Fix: Sanitize and Parse SQL XML Correctly

If the column contains XML fragments, wrap the data in a root node in your SQL query using CAST('<root>' + CAST(XmlCol AS NVARCHAR(MAX)) + '</root>' AS XML). If the error is caused by invalid characters or nested parsing, extract the required values inside SQL using .value() and .nodes() methods, or cast the column to NVARCHAR(MAX) and process it in Power Query using Xml.Document(Text.Clean([XmlCol])).

---

Common Causes of XML Parsing Failures in Excel

Excel processes imported XML through strict validation parsers. If the incoming payload deviates from standard document rules, the connection breaks:

1. XML Fragments (Missing Root Element): Standard XML parsers require every document to possess exactly one parent root element. SQL Server allows XML columns to store “fragments” (e.g., <item>A</item><item>B</item>). When Power Query’s Xml.Document() reads this without an enclosing <root>, it throws DataFormat.Error: Root element is missing.
2. Non-Printable ASCII Control Bytes: Web forms and legacy feeds often save hidden control characters (ASCII 0 through 31) into database text. When wrapped in XML, characters like CHAR(0) (null) or CHAR(11) (vertical tab) violate OpenXML specifications, triggering Fixing “Invalid Cell Value” when SQL returns non-printable ASCII characters..
3. Undeclared XML Namespaces: If your XML elements utilize prefixes (such as <ns:Order>) without an explicit xmlns:ns="http://..." attribute defined within the fragment, the parser stops with an undeclared namespace error.
4. Buffer Truncation: Large XML records stored in XML or NVARCHAR(MAX) fields that exceed driver memory limits can be cut off mid-tag (e.g., <Description>Produc...). The unclosed tag breaks parsing, a symptom detailed in Fixing “Truncated Data” errors when importing SQL VARCHAR(MAX) columns..

[SQL Server XML Column]
           │
           ├── Well-Formed (<root><tag>Val</tag></root>) ──► Parses cleanly via Xml.Document()
           │
           ├── XML Fragment (<tag>A</tag><tag>B</tag>) ────► FAILS: Missing Root Element
           │
           ├── Contains Control Byte (ASCII < 32) ────────► FAILS: Invalid Character
           │
           └── Undeclared Namespace (<ns:tag>Val</ns:tag>) ─► FAILS: Namespace Resolution

---

Diagnostic Steps: Locating Malformed XML

Before implementing a fix, determine whether the problem is structural, character-based, or volume-based:

Step 1: Validate Document Well-Formedness in SQL

Execute this test query in SQL Server Management Studio to locate malformed fragments:

SELECT
    RecordID,
    XmlData
FROM Production.OrderXml
WHERE
    -- Check if wrapping in a root resolves parsing
    TRY_CAST('<root>' + CAST(XmlData AS NVARCHAR(MAX)) + '</root>' AS XML) IS NULL
    AND XmlData IS NOT NULL;

If rows appear, those records contain illegal characters, unescaped ampersands (&), or malformed closing tags that cannot be parsed as XML under any circumstance.

Step 2: Inspect the Power Query Error Step

Open Power Query, navigate to the step where the error surfaces, and click the white space next to the red Error link:

• “Root element is missing”: The column contains XML fragments or empty strings ("").
• “The prefix ‘xyz’ is not defined”: The data has unresolved namespace prefixes. Review Troubleshooting XML Namespace errors in Power Query..
• “An invalid character was found”: Hidden ASCII control characters are embedded in the text nodes.

---

Step-by-Step Remediation Methods

Method 1: Relational Extraction at the SQL Level (Recommended)

The most reliable approach is to extract the required XML elements into standard relational columns before sending the data to Excel. This offloads XML parsing to SQL Server and avoids Power Query overhead entirely:

SELECT
    RecordID,
    -- Extract scalar attributes using XQuery .value()
    XmlPayload.value('(/Order/@ID)[1]', 'INT') AS OrderID,
    XmlPayload.value('(/Order/Customer/text())[1]', 'NVARCHAR(100)') AS CustomerName,
    XmlPayload.value('(/Order/Total/text())[1]', 'DECIMAL(18,2)') AS TotalAmount
FROM Sales.OrdersXml;

Because the output is delivered as standard integers, text, and decimals, Excel imports the table cleanly without requiring any XML decoding functions.

Method 2: Wrapping XML Fragments in a Synthetic Root Node

If you need to pass raw XML fragments into Power Query, enforce well-formedness by injecting a root element during query extraction:

SELECT
    RecordID,
    CAST('<root>' + CAST(XmlPayload AS NVARCHAR(MAX)) + '</root>' AS XML) AS WellFormedXml
FROM Sales.OrdersXml;

In Power Query, parse the column using Xml.Document():

let
    Source = Sql.Database("DatabaseServer", "SalesDB"),
    RawData = Source{[Schema="dbo",Item="OrdersXml"]}[Data],
    // Parse the well-formed XML column
    ParsedXml = Table.AddColumn(RawData, "CustomXml", each Xml.Document([WellFormedXml])),
    // Remove the synthetic root to access child elements
    ExpandedRoot = Table.ExpandTableColumn(ParsedXml, "CustomXml", {"Name", "Value"}, {"NodeName", "NodeValue"})
in
    ExpandedRoot

Method 3: Sanitizing Control Characters in Power Query

If the database contains unescaped text or illegal control characters that break the parser, clean the string before invoking Xml.Document:

let
    Source = Sql.Database("DatabaseServer", "SalesDB"),
    RawTable = Source{[Schema="dbo",Item="RawLogs"]}[Data],

    // Cast to text to allow string cleaning
    ConvertToString = Table.TransformColumnTypes(RawTable, {{"XmlColumn", type text}}),

    // Strip non-printable ASCII control characters
    CleanedText = Table.TransformColumns(ConvertToString, {{"XmlColumn", Text.Clean, type text}}),

    // Parse safe XML strings
    ParseXml = Table.TransformColumns(CleanedText, {{"XmlColumn", each try Xml.Document(_) otherwise null}})
in
    ParseXml

Wrapping the parsing routine in try ... otherwise null ensures that an individual corrupt row does not crash the entire dataset refresh.

---

Verification and Downstream Stability

To confirm that your XML import pipeline is secure:

1. Audit Failed Rows: In Power Query, filter the parsed column for null values after applying the try ... otherwise step to isolate corrupt records for database remediation.
2. Expansion Verification: Expand the resulting table column. Confirm that element attributes and child values align into their designated columns rather than throwing #VALUE! or #CALC! errors.

If your database workflows involve extracting structured attributes from JSON payloads rather than XML, consult Handling “JSON” string expansion errors from SQL FOR JSON outputs.. For structural repair strategies when an Excel file becomes corrupted by external markup, see How to recover a “Corrupt” Excel file using the XML extraction method. and SQL-to-Excel Data Mapping: Fixing Type Mismatches and Schema Breaks..