Excel to XML: Converting Spreadsheets for Data Integration

Many enterprise systems, government portals, and B2B integrations still speak XML. If your data lives in Excel, you'll eventually need to turn those spreadsheets into well-formed XML. This guide explains how the mapping works, how to decide between elements and attributes, and how to validate the result.

How a spreadsheet maps to XML

An Excel sheet is a table: a header row plus data rows. In XML, the natural mapping is a repeating element per row, with one child element per column:

<employees>
  <employee>
    <name>Alice</name>
    <department>Engineering</department>
    <salary>85000</salary>
  </employee>
  <employee>
    <name>Bob</name>
    <department>Sales</department>
    <salary>72000</salary>
  </employee>
</employees>

The sheet name suggests the wrapper element (employees), each row becomes a record element (employee), and each column becomes a field element.

Elements vs. attributes

XML offers two ways to attach a value. Both are valid; choose based on meaning:

<!-- Using child elements -->
<employee>
  <name>Alice</name>
</employee>

<!-- Using an attribute -->
<employee id="1" name="Alice" />

Use elements for	Use attributes for
The actual data/content	Identifiers and metadata (id, type)
Values that may repeat or nest	Short, single values
Anything you may extend later	Stable, non-repeating qualifiers

Method 1: Convert in your browser

The quickest path is to export your Excel sheet to CSV (or upload the workbook), then convert. Smart Data Converter parses the table locally and produces structured output you can adapt to XML — no upload, no server. For complex schemas you'll typically generate a base structure and then refine element names.

Method 2: Convert with Python

import pandas as pd
from xml.sax.saxutils import escape

df = pd.read_excel("employees.xlsx", dtype=str).fillna("")

lines = ["<employees>"]
for _, row in df.iterrows():
    lines.append("  <employee>")
    for col in df.columns:
        tag = col.strip().replace(" ", "_")
        lines.append(f"    <{tag}>{escape(str(row[col]))}</{tag}>")
    lines.append("  </employee>")
lines.append("</employees>")

open("employees.xml", "w", encoding="utf-8").write("\n".join(lines))

Note the use of escape() — this is essential, as we'll see next.

Escaping special characters

Five characters have special meaning in XML and must be escaped inside content:

Character	Escaped as
&	&
<	&lt;
>	&gt;
"	&quot;
'	&apos;

A company name like "Smith & Co" will make the XML invalid unless the ampersand is escaped.

Valid element names

Spreadsheet headers are free-form, but XML element names have rules: they can't start with a number or contain spaces. Sanitize headers before using them as tags — e.g. "Order #" → order_number, "2024 Total" → total_2024.

Validating your XML

Two levels of correctness matter:

• Well-formed: tags are balanced and properly nested, special characters escaped. Any XML parser checks this.
• Valid: the document also conforms to a schema (XSD) that the receiving system defines. If your partner provides an XSD, validate against it before sending.

Common pitfalls

• Unescaped &: the most frequent cause of "invalid XML" errors.
• Invalid tag names: headers with spaces or leading digits.
• Encoding mismatch: declare and save as UTF-8 (<?xml version="1.0" encoding="UTF-8"?>).
• Empty cells: decide whether to emit an empty element, omit it, or use xsi:nil.

Frequently asked questions

Should each column be an element or an attribute?

Default to elements for data values; reserve attributes for identifiers and metadata. Many target schemas dictate the choice for you.

How do I validate the output?

Use an XML validator or an XSD provided by the receiving system. Most IDEs validate XML against a schema on the fly.

Can I go from XML back to a spreadsheet?

Yes — flatten the repeating elements into rows. See flattening structured data to CSV for the same principle applied to nested formats.

---

Excel-to-XML is mostly a disciplined mapping: rows to record elements, columns to fields, with careful escaping and valid names. Validate against a schema when one exists, and your integrations will accept the data on the first try.