Importing XML Into Excel With an XML Map Step by Step

Excel can read XML directly, and for years the way to do it was the XML map. You tell Excel how the elements of a schema line up with cells, then import a data file and the values land where you mapped them. It works well for flat, repeating data, and it is awkward for anything nested. This article explains the model so that you can predict what Excel will do before you click.

What an XML map is

An XML map is a definition stored in the workbook: a schema (an XSD, or one Excel inferred) and a set of links from schema elements to cells. It lives in the file as part of the workbook package, which is a zip of XML parts like any other Office file (reading Office Open XML from docx and xlsx files). The map has two jobs. On import it says where each element's text goes. On export it says how to rebuild the document from the cells (exporting Excel data as XML with a schema).

The mapping unit is the element. A simple element with a single occurrence maps to one cell. A repeating element maps to a column of an Excel table, with one row per occurrence. That distinction is the whole design: a list of records with the same fields becomes a table, and anything that is neither a single value nor a flat list of records has no natural place on a grid.

The import paths

Microsoft's documentation describes two situations.

With a map. If the workbook already has a map and mapped cells, you select a mapped cell, open the Developer tab, choose Import, pick the XML file and Excel fills the cells. This is the route for a recurring report: build the map once, import each new file into the same layout.

Without a map. If you open or import an XML file and no map exists, Excel looks for a schema reference in the file. If there is none, it infers a schema from the data, builds a map and creates an XML table in a new or existing worksheet. The inferred schema is only as good as the data it saw. A field that held numbers in the sample is a number type, an element that occurred once was treated as single-valued, and an element missing from the sample does not exist in the schema (why Excel complains about an XML source with no schema).

You can also import several files into one set of mapped cells by selecting them together, and all of their data is appended. Microsoft notes a limitation: files that share a namespace but follow different schemas can fail or overwrite each other, and the suggested route is the From XML Data Import command, which creates a separate map for each file.

A file that maps well

<orders>
  <order><id>1001</id><customer>Ana Lee</customer><total>19.99</total></order>
  <order><id>1002</id><customer>Bo Chen</customer><total>44.00</total></order>
</orders>

One repeating element (order) with three simple children is the ideal case. Excel creates a table with three columns, id, customer and total, and one row per order. This shape is exactly what a database export or a flat feed produces. If your XML looks like this, the map route is short.

A file that does not

<orders>
  <order id="1001"><customer>Ana Lee</customer>
    <items><item sku="A-100" qty="2"><price>9.50</price></item><item sku="B-220" qty="1"><price>24.00</price></item></items>
  </order>
</orders>

Here the order repeats and each order has repeating items. A grid has one level of repetition per table, so the map either shows a table of items with the order data repeated on every row, or it needs two tables that Excel cannot join for you. Mapping the repeating item and the customer into the same table can make Excel complain that the mapping is not allowed, because the elements are in different repeating contexts. The fix is not an Excel setting. It is to flatten the document first: one row per item with the order id and customer on each row, or two sheets keyed by order id. I did that with Python, counting 3 orders and 3 items in the sample and checking that the item rows add up (flattening hierarchical XML for Excel users).

Refresh: overwrite or append

The XML Map Properties dialog has options that matter for any import you repeat. One governs whether new data overwrites the mapped cells or is appended to the table. Overwrite is right for a report that always shows the current file. Append is right for accumulating records from many files. The common mistake is using append on a file that contains the whole data set each time: every refresh adds the same rows again. Another option controls validating data against the schema on import and on export, and a third whether Excel preserves column filtering and formatting on refresh. Decide them when you build the map, and write them in a note on the sheet, because the next person will not guess.

Types and what Excel does to values

Import brings text from the file into cells and applies the type the schema says. A number in a field typed as decimal becomes a number. A string stays a string. If the schema was inferred, the inferred type decides, and an identifier like 02134 that looked numeric in the sample may become the number 2134. That is the same loss of leading zeros as when a CSV is opened in a spreadsheet (Excel drops leading zeros when opening a CSV). If identifiers matter, supply a schema that types them as strings. Dates follow the schema's date types; an XML date 2026-03-02 maps well when typed as xs:date, and poorly when it is a string.

Limits to know

Microsoft's documentation says Excel exports at most 65,536 rows through an XML map, and lists structures that cannot be exported (choice constructs, mixed content, recursive structures beyond one level, repeating elements in a sequence in some positions). Those limits are about export, but they shape what you can import and send back. The XML tools are not available in Excel for the web or Excel for Mac. And the documentation itself describes XML maps as a legacy feature: for complex or nested XML, it recommends Power Query.

Related reading

For the spreadsheet cluster see moving XML into and out of spreadsheets, and for opening an XML file directly opening XML files in Excel without mangled data. The map feature is described in Microsoft's Map XML elements to cells in an XML Map. Test a path in the XPath tester.

When Power Query is the better route

The XML is nested. Power Query can expand nested records and lists step by step, and keeps the steps so a refresh repeats them.

You need transformation on the way in. Renaming, filtering and type conversion belong in a query, not in a map.

You use Excel for Mac or the web. XML maps are unavailable there; Power Query has its own coverage by version.

The same file is also loaded elsewhere. Convert once with a script (converting XML to Excel with Python) and give people the workbook.