Importing XML to Excel sounds simple. Click Data, pick a file, and the rows appear. Then reality hits. Maybe Excel crashes the moment you open the file. Maybe it imports but leaves you staring at one wide row of node names. Maybe it asks for a schema you have never seen and refuses to go further until you produce one.
This guide walks through every method that actually works in modern Excel: the legacy Developer ribbon path, the modern Power Query path, the Power Query editor for nested structures, and the VBA fallback for files that fight back. You will also learn why imports crash, how to fix corrupted schema mappings, and which method to pick for one-off versus repeating jobs. If you study Excel for certification or work, our Excel skills guide covers the full spreadsheet stack alongside import workflows.
XML refuses to die because every accounting package, every bank, and every government tax portal still publishes data in XML. CSV would be friendlier, but XML preserves the hierarchy of invoices that contain line items, accounts that contain transactions, and contracts that contain clauses. Excel needs to bridge that hierarchy into the flat grid of rows and columns its users actually work in. The three methods below each draw that bridge differently, and picking the right one for your file decides whether the next thirty minutes go smoothly or end in a corrupted workbook.
Excel ships with three distinct routes for pulling XML into a worksheet. Each one solves a different problem. Picking the wrong one is why so many users hit the crash wall on their first try.
The Open as XML Table shortcut is the fastest. File > Open, pick the .xml file, choose As an XML table, done. It works for small, flat XML files where every element is one row and one column. Try it on a nested e-commerce export and Excel will either crash or flatten the structure into something unusable. For an inventory snippet with 200 product rows, this method takes about four seconds.
The Developer ribbon XML Map is the structured route. You import an XSD schema, drag elements onto specific cells, and Excel builds a repeating block. Later you can refresh the map against a new XML file and the data updates in place. This is the right call for monthly bank statement imports, regulatory filings, or any feed where the schema stays fixed but the data changes.
The third route, Power Query, treats XML the same way it treats CSVs, JSON files, and SQL tables. It builds a query, records every transformation step, and replays the entire pipeline whenever you point it at a new file. Power Query understands namespaces, mixed content, and nesting that would break the other two methods. The trade-off is a steeper first run because you must walk through the navigator and expand each nested column manually before Excel knows what shape you want.
Fastest path. Good for small flat files under 50 MB with predictable element nesting. No schema mapping required.
Structured path. Loads an XSD schema, maps elements to cells, supports refresh. Best for repeating monthly or quarterly imports.
Power user path. Parses any nested or messy XML, handles namespaces, and transforms before loading. Best for complex feeds.
Use this when the XML file is small, the structure is shallow, and you only need the data once. Save the file locally first because Excel struggles with network-mounted XML over slow links.
Open Excel with a blank workbook. Click File > Open, navigate to the .xml file, and select it. Excel pops a dialog with three options. Pick As an XML table and click OK. If Excel warns that the file refers to a schema, click OK again to let Excel build one on the fly. The data lands in row 2, column A.
You will see element names as column headers. Attributes appear with an @ prefix, so an XML attribute called id shows up as @id. Repeating child elements get flattened into rows. If the file mixes single elements with repeating blocks, Excel will leave the singletons in the header area and put the repeats below. This sometimes looks broken on first import, but the layout is correct.
One quirk catches almost everyone the first time. When you click Save after editing, Excel asks whether to save as a workbook or back to XML. Pick workbook unless you specifically need round-trip XML export. Saving back to XML strips any formatting, charts, or extra columns you added during the session.
The XML Map approach pays off when the same file format arrives every week. Once you build the map, refreshing takes one click.
Enable the Developer tab if you have not already. Go to File > Options > Customize Ribbon and tick the Developer checkbox in the right column. Click OK. The Developer tab now sits in the ribbon between View and Help.
On the Developer tab, click Source. The XML Source pane opens on the right. Click XML Maps, then Add, and browse to your XSD schema file. If you only have the XML file, Excel can infer a schema from it. Open the XML file in Notepad first to confirm it is valid. Once the map loads, drag elements from the source pane onto cells in the worksheet. Each element becomes a column header.
To pull in data, click Import on the Developer tab and pick your XML file. The data populates the mapped cells. Next month, hit Refresh All on the Data tab to pull a fresh file into the same layout. Pair this with a pivot table to summarise the refreshed data automatically.
The XML Map quietly stores its definition inside the workbook file itself. That means a colleague who opens your .xlsx on a different machine inherits the same map. They can refresh against their local copy of the XML file without rebuilding anything. Government tax workbooks and audit templates rely on this portability to keep dozens of accountants in sync.
Power Query is the modern answer to XML import. It handles namespaces, nested records, mixed content, and files large enough to crash the legacy parser. Microsoft built it into Excel from 2016 onward, and every newer release improves the XML connector.
On the Data tab, click Get Data > From File > From XML. Pick your file. Power Query analyses the structure and shows a navigator preview. You will see each top-level element listed. Click one to preview the rows below. If the rows show Table or Record placeholders instead of values, the data is nested. Click Transform Data to open the editor.
Inside the editor, each cell that shows Table can be expanded. Click the expand arrow on the column header, tick the child fields you want, and the data unrolls into proper columns. Repeat for every nested level. Power Query records every step in the Applied Steps pane on the right, so next month you load a new XML file and the same transformations replay automatically.
Power Query also exposes the raw M language behind every step. Click Advanced Editor and you can write or paste M code directly. That matters when you need to handle conditional logic the GUI does not expose, such as filtering nodes based on attribute values or merging two XML files into one dataset before loading.
Sometimes the file refuses to load through any of the three native paths. Maybe it has an unusual encoding, maybe the schema references external DTDs that no longer exist, or maybe you need to filter elements as they load. VBA gives you full control through the MSXML2.DOMDocument library.
Open the VBA editor with Alt+F11. Insert a new module. Reference Microsoft XML, v6.0 from Tools > References. Write a Sub that creates a DOMDocument object, loads the XML file with the Load method, then iterates the SelectNodes results with an XPath expression. Each node becomes a row, each attribute or child element becomes a cell. This approach skips the schema entirely and gives you exactly the data you ask for.
VBA is also the right tool when you need to import dozens of XML files from a folder in one go. Loop through the folder with Dir(), open each file, append rows, and close. For deeper automation patterns, see our guide to Excel VBA macros which covers loops, error handling, and file system access in detail.
One overlooked VBA trick handles broken schema references. Set doc.setProperty "ProhibitDTD", False before calling Load and the parser stops choking on legacy DOCTYPE declarations that point at long-dead URLs. Most government XML files from before 2015 need this single line to load at all.
If you import XML into a workbook that already has thousands of formulas, set Application.Calculation = xlCalculationManual before the import. Excel otherwise recalculates the entire workbook after every row, which can turn a 5-second import into a 5-minute one. Restore xlCalculationAutomatic after the import finishes. The same trick speeds up CSV imports, Power Query refreshes, and any macro that writes hundreds of rows at once.
For VBA-driven imports, wrap the entire load in Application.ScreenUpdating = False as well. That single line stops Excel from redrawing the worksheet after every cell write, which on large XML files saves another 30 to 50 percent on total import time.
An XML file that crashes Excel is almost always invalid in a way the editor never showed. Validate the file first and you save yourself an hour of guessing. The fastest free validators include the W3C XML validator online and the xmllint command line tool that ships with most Linux and macOS installs and is available for Windows via Chocolatey.
Run xmllint --noout file.xml from a terminal. The tool prints nothing if the file is well-formed and prints precise line and column errors if it is not. Common offenders include unclosed tags from a truncated download, stray ampersands in product descriptions, and namespace prefixes that were defined in an earlier element but reused without redeclaration.
Validation against a real XSD is a separate step. Run xmllint --schema schema.xsd file.xml --noout and the tool reports any element that violates the schema. This catches data type mismatches like text where a number was expected, missing required attributes, and elements that appear too many or too few times. Fix these before importing or Excel will silently truncate the offending rows.
After import, XML data often arrives with leading or trailing spaces around element values. Use TRIM() in a helper column or run remove spaces in Excel with Find & Replace to clean every cell at once. Date strings frequently come in as text in ISO format like 2026-05-14 and need date format conversion before they sort correctly.
When Excel offers to infer a schema from an XML file, it scans the file once and guesses the data types for each element. Numbers become numbers, dates become dates if the format matches, and everything else becomes text. The inferred schema is fine for one-time imports. It breaks the moment a new XML file introduces an element that did not appear in the original.
A real XSD schema authored alongside the data source survives every change because it declares every possible element up front. Banks, government agencies, and accounting software publish XSDs for exactly this reason. Always prefer the official XSD when one exists. Save it next to your workbook and add it via Developer > Source > XML Maps before importing any data files.
To test your map against changes, run Developer > Import on next month's file and watch for warnings in the Excel status bar. A green tick means everything mapped cleanly. A yellow warning means new elements appeared in the XML that have no home in the worksheet, and you should extend the map by dragging the new elements onto fresh columns.
If your industry publishes schema updates each year, version the XSDs alongside your workbook in a folder named by date. Pointing the XML Map at the previous year's schema lets you compare imports across periods without worrying about new fields breaking the layout. Then when you confirm the new schema works, swap the reference and refresh.
Pick fast for small files. If your XML is under 10 MB and you need the data once, use File > Open and select As an XML table. Total time: under a minute. No setup. No mapping. Just data.
Pick structured for recurring imports. If the same file arrives every Monday, build an XML Map. Spend twenty minutes setting it up once, then click Refresh on Mondays forever. The map travels with the workbook, so colleagues opening it on different machines see the same structure.
Pick Power Query for everything else. Nested structures, namespaces, multi-megabyte files, files that need filtering or joining with another data source before load. Power Query records every step, replays them on new files, and survives the messy edge cases that crash the legacy XML import every time. For an end-to-end refresher on the editor itself, our Excel Power Query guide walks through the full transform stack.
One more honest call: if the same file format hits your inbox five times a week and accuracy matters more than speed, learn Power Query first. The hour you invest in understanding navigator expansion and applied steps pays back within two weeks of saved manual work. The other two methods will always be there for one-off imports, but Power Query becomes your daily driver.
Once data lands in Excel, do not jump straight to analysis. Spend five minutes confirming the import succeeded end-to-end. Sort the date column to catch any rows where dates parsed as text. Check the row count against the source XML using a quick grep on the closing tag. Spot-check three rows at random against the raw XML to confirm element-to-column alignment. These three checks catch the silent failures that turn a clean import into a downstream reporting bug.
Then save the workbook with a clear name that records the source file, import method, and date. Something like bank-statements-xmlmap-2026-05.xlsx tells the next person opening the file exactly what they are looking at without needing to ask. Future-you on a Monday morning two months from now will thank present-you for that small habit.