Convert XML to Excel with pandas read_xml

Plenty of systems still export XML: ERP and accounting packages, bank statement feeds, government data portals, anything built around SOAP. The people who need that data want it in Excel. pandas has read XML since version 1.3 with pd.read_xml, which turns repeating elements into rows and their attributes and child elements into columns. It handles flat XML in one call. Real exports are rarely flat — they have namespaces, nested line items, and codes that must keep their leading zeros — so this page, part of Handling Excel File Formats and Conversions, works through a realistic order export and writes it to a workbook with one sheet per record type.

Prerequisites

pip install pandas lxml openpyxl

orders.xml has a root <export> element with a default namespace, two <order> elements with id and date attributes, a <customer> element carrying a code attribute, a <total>, and a nested <items> block with one <item> per line.

Step 1 — Read the top-level records

With no arguments, read_xml treats the children of the root element as rows. Attributes and child elements of each row become columns:

import pandas as pd

orders = pd.read_xml("orders.xml")
print(orders)
print(orders.dtypes.to_dict())

The id, date, customer and total columns are right, and pandas has inferred integers and floats. Three things are not. The items column holds only whitespace, because the nested block has no text of its own. The customer code and the total's currency are missing: they are attributes of child elements, and read_xml only collects attributes of the row element itself. And the default namespace has quietly worked here only because no XPath was involved — the next step needs one.

Step 2 — Select elements with XPath and namespaces

xpath= chooses which elements become rows. The export declares a default namespace (xmlns="urn:example:orders"), so every element name belongs to it, and an XPath like //order matches nothing. Map a prefix to the namespace and use it:

ns = {"o": "urn:example:orders"}

orders = pd.read_xml("orders.xml", xpath="//o:order", namespaces=ns)
customers = pd.read_xml("orders.xml", xpath="//o:customer", namespaces=ns, dtype={"code": str})
print(customers)

The prefix is only a label inside your code — o or ord or anything else — as long as it maps to the namespace URI written in the file. Reading <customer> elements on their own picks up the code attribute and the element's text. dtype={"code": str} matters: without it, pandas reads "00123" as the number 123, the same inference problem Keep Leading Zeros When Reading Excel with pandas describes for spreadsheets.

If lxml is not available, parser="etree" uses the standard library, which supports only a subset of XPath: paths must be relative, such as .//o:order, and functions like ancestor:: are unavailable.

Step 3 — Keep line items linked to their order

The line items are the awkward part. read_xml(xpath="//o:item") returns the three items correctly but has no way to say which order each belongs to — it reads one element type at a time and does not look at parents. Walk the tree yourself with lxml and copy the parent's key fields onto each child row:

from lxml import etree

tree = etree.parse("orders.xml")
rows = []
for order in tree.iterfind(".//o:order", namespaces=ns):
    customer = order.find("o:customer", namespaces=ns)
    for item in order.iterfind(".//o:item", namespaces=ns):
        rows.append({"order_id": int(order.get("id")), "customer_code": customer.get("code"),
                     "sku": item.get("sku"), "qty": int(item.get("qty")), "price": float(item.get("price"))})
items = pd.DataFrame(rows)
print(items)

Converting types while building the rows keeps the customer code as text and the quantities as numbers. Now the Items table can be joined back to Orders on order_id, and a pivot of quantity by customer is one line away.

Step 4 — Or flatten with an XSLT stylesheet

read_xml can apply an XSLT stylesheet before parsing, which is the declarative way to reshape nested XML. The stylesheet below emits one flat <row> per item, pulling the order id and customer name from the item's ancestors:

import io

XSLT = b"""<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:o="urn:example:orders">
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="/">
    <rows>
      <xsl:for-each select="//o:item">
        <row>
          <order_id><xsl:value-of select="ancestor::o:order/@id"/></order_id>
          <customer><xsl:value-of select="ancestor::o:order/o:customer"/></customer>
          <sku><xsl:value-of select="@sku"/></sku>
          <qty><xsl:value-of select="@qty"/></qty>
          <price><xsl:value-of select="@price"/></price>
        </row>
      </xsl:for-each>
    </rows>
  </xsl:template>
</xsl:stylesheet>"""

print(pd.read_xml("orders.xml", stylesheet=io.BytesIO(XSLT)))

XSLT needs lxml and is worth it when the stylesheet already exists — some vendors ship one with their export — or when the flattening rules are complex enough that you want them in a separate, testable file. For a one-off conversion, the Python loop in Step 3 is easier to read and debug.

Step 5 — Write the workbook

Put each record type on its own sheet, parse dates on the way, and give the columns sensible widths:

orders = pd.read_xml("orders.xml", xpath="//o:order", namespaces=ns, parse_dates=["date"])
orders = orders.drop(columns=["items"]).merge(customers.rename(columns={"code": "customer_code"}), on="customer")

with pd.ExcelWriter("orders.xlsx", engine="openpyxl") as writer:
    orders.to_excel(writer, sheet_name="Orders", index=False)
    items.to_excel(writer, sheet_name="Items", index=False)
    for ws in writer.book.worksheets:
        for column in ws.columns:
            width = max(len(str(c.value)) for c in column if c.value is not None)
            ws.column_dimensions[column[0].column_letter].width = width + 2

print(pd.ExcelFile("orders.xlsx").sheet_names, pd.read_excel("orders.xlsx", sheet_name="Items", dtype={"customer_code": str}).shape)

Dropping the empty items column removes the whitespace junk from Step 1, and merging in the customer codes puts every order field on one row. Reading the file back with dtype={"customer_code": str} confirms the leading zeros survived into the workbook. Write Multiple DataFrames to One Excel File covers the multi-sheet writer in more depth.

Step 6 — Stream files too large for memory

read_xml normally builds the whole document tree before extracting rows, and XML trees take several times the file's size in memory. For multi-gigabyte exports, iterparse streams the file and keeps only the element and fields you name:

lines = pd.read_xml("orders.xml", iterparse={"item": ["sku", "qty", "price"]})
print(lines)

The element name is given without a namespace prefix, and only the listed attributes or child elements are kept. iterparse cannot combine it with a parent's fields, so for very large files with nested records, stream with lxml.etree.iterparse directly, building rows as in Step 3 and clearing each processed element to keep memory flat. Excel's own limit of 1,048,576 rows per sheet still applies to the result; Fix "This sheet is too large" When Writing Excel with pandas covers what to do when the XML holds more.

Choosing the approach for a new export

Every XML export is shaped differently, but the choice of tool follows from a few questions about it. Look at a sample file first — open it in a browser or an editor, find the repeating element, check the root for an xmlns= declaration and see how deep the nesting goes — and the decision is usually quick:

Write the chosen reading code as a function that takes the file path and returns DataFrames, and keep the XPath expressions and namespace URI as named constants at the top of the module. When the vendor changes the export — and they do, usually by adding a namespace version or a wrapper element — the fix is then one constant rather than a hunt through the script.

Common pitfalls

• Ignoring the default namespace. //order matches nothing when the file declares xmlns="…"; map a prefix.
• Expecting child attributes as columns. Only the row element's own attributes are collected; read child elements separately or walk the tree.
• Losing leading zeros. Pass dtype for codes and IDs.
• Absolute XPath with parser="etree". Use .// paths with the standard-library parser.

Performance notes

lxml parses tens of megabytes per second, and for typical exports the conversion is dominated by writing the workbook, not by reading XML. Memory is the real constraint: a 500 MB XML file can need several gigabytes as a tree, which is where iterparse — or a streaming lxml loop — becomes necessary rather than optional.

Conclusion

pd.read_xml turns repeating XML elements into rows, with XPath choosing the elements and a namespaces mapping required whenever the file declares a default namespace. Read each record type separately, keep codes as text with dtype, link nested items to their parent by walking the tree with lxml or applying an XSLT stylesheet, write one sheet per record type, and switch to iterparse for files that do not fit in memory.

Frequently asked questions

What does pd.read_xml need to be installed? It uses lxml by default, so install lxml. With parser="etree" it falls back to Python's standard library, which supports only a subset of XPath: paths must be relative, such as .//order.

Why does read_xml return nothing, or an XPath error, for my file? Usually because the XML has a default namespace (xmlns="..."). Element names then belong to that namespace, and the XPath must use a prefix mapped in namespaces=, for example xpath="//o:order" with namespaces={"o": "urn:example:orders"}.

How do I keep the order number on each line item? read_xml reads one element type at a time and cannot add attributes from parent elements. Walk the tree with lxml and copy the parent's fields onto each child row, or use an XSLT stylesheet with read_xml's stylesheet argument.

How do I convert an XML file that is too large for memory? Pass iterparse={"item": "sku", "qty", "price"} to read_xml. It streams the file and keeps only the named element and fields, instead of building the whole document tree.

Related

• Up one level: Handling Excel File Formats and Conversions — the formats data moves through.
• Convert an Excel Sheet to JSON with Python — the other common interchange format.
• Fetch API Data into Excel with Python requests — when the XML arrives from a web service.
• Merge Two Excel Files on a Common Column in Python — joining the Orders and Items sheets.