Pandas to_xml(): Export DataFrames and Verify XML Files

Use to_xml() to export a DataFrame as repeating XML records. Choose the receiving system's tags and fields, then verify the output.

Show Table of Contents ↓

Basic example: return XML text 🔝

These synthetic student records retain the original teaching dataset. Without a destination, to_xml() returns a string. Explicit Root and Row names reproduce the original layout; the actual defaults are data and row. All examples use the built-in etree parser unless stated otherwise.

import pandas as pd
import xml.etree.ElementTree as ET
import tempfile
import sqlite3
import gzip
from pathlib import Path
from io import StringIO, BytesIO
df = pd.DataFrame({
    "id": [1, 2, 3], "name": ["John Deo", "Max Ruin", "Arnold"],
    "class": ["Four", "Three", "Three"], "mark": [75, 85, 55],
    "gender": ["female", "male", "male"]})
my_xml = df.to_xml(index=False, root_name="Root", row_name="Row", parser="etree")
print(my_xml)
assert len(ET.fromstring(my_xml)) == 3

Expected output

<?xml version="1.0" encoding="utf-8"?>
<Root>
  <Row>
    <id>1</id>
    <name>John Deo</name>
    <class>Four</class>
    <mark>75</mark>
    <gender>female</gender>
  </Row>
  <Row>
    <id>2</id>
    <name>Max Ruin</name>
    <class>Three</class>
    <mark>85</mark>
    <gender>male</gender>
  </Row>
  <Row>
    <id>3</id>
    <name>Arnold</name>
    <class>Three</class>
    <mark>55</mark>
    <gender>male</gender>
  </Row>
</Root>

Write a DataFrame to an XML file 🔝

Passing a destination writes the document and returns None. This portable example creates a temporary directory and reads the file back. For your own folder, use a relative Path or a Windows raw path such as Path(r"E:\testing\data") / "student_output.xml"; the parent folder must exist. See Python XML.

with tempfile.TemporaryDirectory(dir=Path.cwd()) as folder:
    target = Path(folder) / "student_output.xml"
    result = df.to_xml(target, index=False, root_name="Root", row_name="Row", parser="etree")
    loaded = pd.read_xml(target, parser="etree")
    assert result is None and loaded.equals(df)
    print("File written; rows:", len(loaded))

Expected output

File written; rows: 3

Customize root, row, declaration and indentation 🔝

root_name and row_name name XML elements, not DataFrame columns. xml_declaration controls the header; pretty_print controls indentation. Use valid XML names.

custom = df.to_xml(index=False, root_name="Students", row_name="Student",
    xml_declaration=True, pretty_print=True, parser="etree")
print(custom)
tree = ET.fromstring(custom)
assert tree.tag == "Students" and all(row.tag == "Student" for row in tree)
compact = df.head(1).to_xml(index=False, root_name="Students", row_name="Student",
    xml_declaration=False, pretty_print=False, parser="etree")
print("Without declaration:", compact)
assert not compact.startswith("<?xml")

Expected output

<?xml version="1.0" encoding="utf-8"?>
<Students>
  <Student>
    <id>1</id>
    <name>John Deo</name>
    <class>Four</class>
    <mark>75</mark>
    <gender>female</gender>
  </Student>
  <Student>
    <id>2</id>
    <name>Max Ruin</name>
    <class>Three</class>
    <mark>85</mark>
    <gender>male</gender>
  </Student>
  <Student>
    <id>3</id>
    <name>Arnold</name>
    <class>Three</class>
    <mark>55</mark>
    <gender>male</gender>
  </Student>
</Students>
Without declaration: <Students><Student><id>1</id><name>John Deo</name><class>Four</class><mark>75</mark><gender>female</gender></Student></Students>

to_xml() parameter choices 🔝

Parameter	Default	Purpose
`path_or_buffer`	None	Return text, or write to a path/buffer.
`index`	True	Include row labels; False omits them.
`root_name / row_name`	data / row	Names of root and repeating record elements.
`xml_declaration / pretty_print`	True / True	Include XML header / indent output.
`parser`	lxml	lxml requires its optional package; etree uses the standard library.
`attr_cols / elem_cols`	None / None	Choose row attributes and child fields explicitly.
`namespaces / prefix`	None / None	Declare namespaces and choose a declared prefix.
`na_rep`	None	Missing values become empty elements unless a representation is supplied.
`encoding`	utf-8	Output character encoding.
`stylesheet`	None	XSLT 1.0 transformation; requires lxml.
`compression / storage_options`	infer / None	Compress supported file paths / configure remote storage.

Include the DataFrame index 🔝

index defaults to True. Include it when row labels carry meaning; omit disposable numbering for interchange.

indexed = df.head(2).to_xml(index=True, root_name="Root", row_name="Row", parser="etree")
print(indexed)
assert [row.findtext("index") for row in ET.fromstring(indexed)] == ["0", "1"]

Expected output

<?xml version="1.0" encoding="utf-8"?>
<Root>
  <Row>
    <index>0</index>
    <id>1</id>
    <name>John Deo</name>
    <class>Four</class>
    <mark>75</mark>
    <gender>female</gender>
  </Row>
  <Row>
    <index>1</index>
    <id>2</id>
    <name>Max Ruin</name>
    <class>Three</class>
    <mark>85</mark>
    <gender>male</gender>
  </Row>
</Root>

Write attributes and child elements 🔝

attr_cols selects attributes and elem_cols selects children. Explicit lists avoid accidentally dropping needed fields. With index=True, include index in the chosen column lists if required.

mixed = df.to_xml(index=False, root_name="Students", row_name="Student",
    attr_cols=["id"], elem_cols=["name", "class", "mark", "gender"], parser="etree")
print(mixed)
first = ET.fromstring(mixed)[0]
assert first.attrib == {"id": "1"} and first.findtext("name") == "John Deo"

Expected output

<?xml version="1.0" encoding="utf-8"?>
<Students>
  <Student id="1">
    <name>John Deo</name>
    <class>Four</class>
    <mark>75</mark>
    <gender>female</gender>
  </Student>
  <Student id="2">
    <name>Max Ruin</name>
    <class>Three</class>
    <mark>85</mark>
    <gender>male</gender>
  </Student>
  <Student id="3">
    <name>Arnold</name>
    <class>Three</class>
    <mark>55</mark>
    <gender>male</gender>
  </Student>
</Students>

Export a namespace and read it back 🔝

Namespace URIs identify vocabularies; they need not be downloadable pages. This synthetic URI is for practice. Prefixes must be declared. The reader uses a prefix mapping in XPath; see read_xml().

uri = "https://example.com/students"
namespaced = df.to_xml(index=False, root_name="Students", row_name="Student",
    namespaces={"school": uri}, prefix="school", parser="etree")
print(namespaced)
restored = pd.read_xml(StringIO(namespaced), xpath=".//school:Student",
    namespaces={"school": uri}, parser="etree")
assert restored.equals(df)

Expected output

<?xml version="1.0" encoding="utf-8"?>
<school:Students xmlns:school="https://example.com/students">
  <school:Student>
    <school:id>1</school:id>
    <school:name>John Deo</school:name>
    <school:class>Four</school:class>
    <school:mark>75</school:mark>
    <school:gender>female</school:gender>
  </school:Student>
  <school:Student>
    <school:id>2</school:id>
    <school:name>Max Ruin</school:name>
    <school:class>Three</school:class>
    <school:mark>85</school:mark>
    <school:gender>male</school:gender>
  </school:Student>
  <school:Student>
    <school:id>3</school:id>
    <school:name>Arnold</school:name>
    <school:class>Three</school:class>
    <school:mark>55</school:mark>
    <school:gender>male</school:gender>
  </school:Student>
</school:Students>

Represent missing values and escape text 🔝

XML serializers escape ampersands and angle brackets in values. Missing values normally produce empty elements; na_rep supplies literal text, which does not automatically become missing on reimport. XML is not a complete Pandas dtype schema.

notes = pd.DataFrame({"id": ["001", "002"], "note": ["A & B <ready>", None]})
text = notes.to_xml(index=False, na_rep="MISSING", parser="etree")
print(text)
root = ET.fromstring(text)
assert root[0].findtext("note") == "A & B <ready>"
assert root[1].findtext("note") == "MISSING"
checked = pd.read_xml(StringIO(text), parser="etree", dtype={"id": "string"})
assert checked["id"].tolist() == ["001", "002"]

Expected output

<?xml version="1.0" encoding="utf-8"?>
<data>
  <row>
    <id>001</id>
    <note>A &amp; B &lt;ready&gt;</note>
  </row>
  <row>
    <id>002</id>
    <note>MISSING</note>
  </row>
</data>

Write to a byte buffer 🔝

A BytesIO buffer accepts the encoded XML document. Decode its content as UTF-8 when a component needs text. to_xml() returns None when it writes to a buffer.

buffer = BytesIO()
result = df.head(1).to_xml(buffer, index=False, parser="etree")
print(buffer.getvalue().decode("utf-8"))
assert result is None and ET.fromstring(buffer.getvalue())[0].findtext("id") == "1"

Expected output

<?xml version="1.0" encoding="utf-8"?>
<data>
  <row>
    <id>1</id>
    <name>John Deo</name>
    <class>Four</class>
    <mark>75</mark>
    <gender>female</gender>
  </row>
</data>

Write compressed UTF-8 XML 🔝

A .gz destination allows inferred gzip compression. Relative paths and temporary folders work on Windows and Colab. Use an explicit encoding when exchanging non-ASCII text.

with tempfile.TemporaryDirectory(dir=Path.cwd()) as folder:
    target = Path(folder) / "students.xml.gz"
    df.to_xml(target, index=False, encoding="utf-8", parser="etree")
    with gzip.open(target, "rt", encoding="utf-8") as stream:
        root = ET.fromstring(stream.read())
    assert len(root) == 3
    print("Compressed XML records:", len(root))

Expected output

Compressed XML records: 3

Database table to XML file 🔝

Read a student table from SQLite or MySQL into a DataFrame, then export it. This self-contained SQLite example replaces machine-specific database paths. For MySQL, install SQLAlchemy and a compatible driver, create an engine with your own connection details, and read through a managed connection. Never put credentials in a shared notebook.

with sqlite3.connect(":memory:") as connection:
    df.to_sql("student", connection, index=False, if_exists="replace")
    database_df = pd.read_sql_query("SELECT id, name, class, mark, gender FROM student ORDER BY id", connection)
with tempfile.TemporaryDirectory(dir=Path.cwd()) as folder:
    target = Path(folder) / "student.xml"
    database_df.to_xml(target, parser="etree", index=False,
        root_name="Students", row_name="Student", xml_declaration=True, pretty_print=True)
    checked = pd.read_xml(target, parser="etree")
    assert checked.equals(df)
    print("Data successfully exported; checked rows:", len(checked))

Expected output

Data successfully exported; checked rows: 3

Practical export: validate sales records first 🔝

This synthetic sales policy requires a nonblank ID and a nonnegative numeric amount. Keep rejected rows for review, preserve leading-zero identifiers, and verify accepted records after serialization. See Data Cleaning and CSV export.

raw = pd.DataFrame({"id": ["001", "002", "003", "004"],
    "amount": ["5.25", "20", "pending", "-8"]})
clean = raw.copy()
clean["amount"] = pd.to_numeric(raw["amount"], errors="coerce")
valid = raw["id"].notna() & raw["id"].str.strip().ne("") & clean["amount"].ge(0).fillna(False)
accepted = clean.loc[valid].copy()
review = raw.loc[~valid].copy()
text = accepted.to_xml(index=False, root_name="Orders", row_name="Order", parser="etree")
checked = pd.read_xml(StringIO(text), parser="etree", dtype={"id": "string"})
print(text)
print("Review IDs:", review["id"].tolist())
print("Accepted total:", checked["amount"].sum())
assert checked["id"].tolist() == ["001", "002"]
assert checked["amount"].sum() == 25.25
assert len(accepted) + len(review) == len(raw)

Expected output

<?xml version="1.0" encoding="utf-8"?>
<Orders>
  <Order>
    <id>001</id>
    <amount>5.25</amount>
  </Order>
  <Order>
    <id>002</id>
    <amount>20.0</amount>
  </Order>
</Orders>
Review IDs: ['003', '004']
Accepted total: 25.25

Common errors and export limits 🔝

Missing lxml: choose parser="etree" for these examples, or install lxml to use XSLT. Invalid element names: rename columns that contain spaces or start with digits before export. Missing folder: create the parent directory before writing. Lost columns: inspect attr_cols and elem_cols together. Lost ID zeros: supply dtype when reading. Large exports: to_xml() builds a document; it has no chunksize writer. Nested structures: it exports a flat record layout; construct a tree explicitly for a custom nested schema. Use a receiving-system schema validator when one is supplied. For a desktop workflow, see Tkinter with Pandas.

Questions and exercise 🔝

What does to_xml() do?
Which parameters name the root and row elements?
What is the default index behavior?
Export students with marks at least 75 to a Students root and Student rows, without an index. Verify the number of rows.

Try the exercise before opening the solution.

Exercise solution 🔝

to_xml() serializes records; root_name and row_name name the elements, and index defaults to True. Filter first, then export and inspect the resulting tree.

selected = df.loc[df["mark"].ge(75)].copy()
answer = selected.to_xml(index=False, root_name="Students", row_name="Student", parser="etree")
print(answer)
print("Selected rows:", len(selected))
assert len(ET.fromstring(answer)) == 2
assert ET.fromstring(answer)[0].find("index") is None
# Optional Colab download (run after choosing a permanent destination):
# selected.to_xml("student_export.xml", index=False, parser="etree")
# from google.colab import files
# files.download("student_export.xml")

Expected output

<?xml version="1.0" encoding="utf-8"?>
<Students>
  <Student>
    <id>1</id>
    <name>John Deo</name>
    <class>Four</class>
    <mark>75</mark>
    <gender>female</gender>
  </Student>
  <Student>
    <id>2</id>
    <name>Max Ruin</name>
    <class>Three</class>
    <mark>85</mark>
    <gender>male</gender>
  </Student>
</Students>
Selected rows: 2

Practice in Google Colab 🔝

Open in Google Colab View on GitHub

Run examples in order, inspect XML, then change tags and records. Save a copy to keep edits.

Reference: Pandas DataFrame.to_xml documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here