Converting CSV to XML With a Template That Escapes Correctly

Sometimes the system you are feeding wants XML and you have a CSV. The job is easy to sketch: for each row, write an element, for each column, a child. The sketch that most people write first is a string template, and it works until a customer is named "Tom & Jerry". Here is how it fails, how to fix it, and the better way.

The data

id,name,note
1,"Tom & Jerry","a < b"
2,Ana,
3,"Lee ""Q"" Sons","bell"

(The third note holds an actual bell character, which cannot be shown here.) Read with csv.DictReader, each row is a dictionary, and the quotes in Lee "Q" Sons have already been undone by the CSV reader, so what you have in memory is the text, not the CSV syntax. That matters: you are moving from one escaping system to another, and the text in the middle is raw.

The template that breaks

tpl = '<row id="{id}"><name>{name}</name><note>{note}</note></row>'
naive = "<rows>" + "".join(tpl.format(**r) for r in rows[:2]) + "</rows>"
print(naive)
etree.fromstring(naive)

<rows><row id="1"><name>Tom & Jerry</name><note>a < b</note></row><row id="2"><name>Ana</name><note></note></row></rows>
naive -> xmlParseEntityRef: no name, line 1, column 30

The text went into the markup as it was. XML reserves & and < in text, so the parser stopped at the ampersand in column 30 and rejected the whole document. Note the failure mode: it is loud only if you parse the output. A pipeline that writes the file and ships it to a partner finds out from the partner, and the partner's error message is usually less specific than this one. A name like O'Neil is harmless in text but breaks an attribute delimited by single quotes, and a double quote breaks one delimited by double quotes, so the attribute id="{id}" has the same problem as the text.

Fix 1: escape every value

from xml.sax.saxutils import escape
safe = "<rows>" + "".join(
    tpl.format(**{k: escape(v, {'"': "&quot;"}) for k, v in r.items()}) for r in rows[:2]) + "</rows>"

<rows><row id="1"><name>Tom &amp; Jerry</name><note>a &lt; b</note></row><row id="2"><name>Ana</name><note></note></row></rows>

escape replaces &, < and >, and the optional dictionary adds the double quote for use inside an attribute. That makes the template valid, and the output parses. It is acceptable for a flat, fixed shape. What it does not do is stop you from forgetting to call escape on the next column you add, or handle characters that XML forbids.

Fix 2: build elements with lxml

The better answer is not to write markup at all. The library knows the rules:

import csv, io
from lxml import etree
root = etree.Element("rows")
for r in csv.DictReader(io.StringIO(data)):
    row = etree.SubElement(root, "row", id=r["id"])
    etree.SubElement(row, "name").text = r["name"]
    if r["note"]:
        try:
            etree.SubElement(row, "note").text = r["note"]
        except ValueError as e:
            print("lxml refuses row", r["id"], "->", e)
print(etree.tostring(root, pretty_print=True, encoding="unicode"))

lxml refuses row 3 -> All strings must be XML compatible: Unicode or ASCII, no NULL bytes or control characters
<rows>
  <row id="1">
    <name>Tom &amp; Jerry</name>
    <note>a &lt; b</note>
  </row>
  <row id="2">
    <name>Ana</name>
  </row>
  <row id="3">
    <name>Lee "Q" Sons</name>
    <note/>
  </row>
</rows>

There is no escape call anywhere, and & and < appear where they should. The quotes in Lee "Q" Sons need no treatment in element text. Row 2 has no note element because the cell was empty and the loop skips empty cells. Row 3 demonstrates the other benefit: the bell character in the note is a control character that XML 1.0 does not allow at all, not even as a character reference, and lxml raised ValueError when the value was assigned. A template would have written the byte straight into the file, and the document would not be well-formed; a strict parser rejects it with an "invalid character" error, whose repair is described in invalid character in XML. Look at the leftover <note/> in row 3, too: the element had already been created when the assignment failed, so it stayed in the tree empty. Create the element only after the value is known to be good, or remove it in the handler.

When the library raises, you have a choice, and it belongs in the code as a decision: strip the character, replace it with a visible marker, or reject the row to a log. I remove control characters other than tab, line feed and carriage return with a regular expression, and I count them, because a feed that has 40 of them in a day is telling you about a problem in the source system.

Empty cells

Row 2 has no note. In the CSV that is an empty string, and a loop that creates every element produces <note/> for it. Whether to emit an empty element or to omit it is a design decision, because to a consumer they can mean different things (handling optional XML elements and missing values). My rule is to omit an element when the cell is empty, unless the receiving schema requires it, and to say so in the documentation. Write the rule once in a helper (add(row, "note", value) which does nothing for an empty value) so it applies to every column the same way.

Shortcut for flat tables

pandas writes XML from a DataFrame:

import pandas as pd
print(pd.read_csv(io.StringIO("id,name\n1,Ana\n2,Bo\n")).to_xml(index=False, root_name="rows", row_name="row"))

<?xml version='1.0' encoding='utf-8'?>
<rows>
  <row>
    <id>1</id>
    <name>Ana</name>
  </row>
  <row>
    <id>2</id>
    <name>Bo</name>
  </row>
</rows>

One line, valid output, and the escaping and the declaration are done. index=False matters: without it you get an extra index element for the DataFrame row number. The function has parameters for attributes (attr_cols) and namespaces, and for a flat export it is reasonable. For anything with nesting, repeated children or a vocabulary dictated by a schema, you are back to building elements. Remember that reading the CSV with pandas applies type inference, so an id 00123 becomes 123 unless you pass dtype=str, a mistake that is easy to miss because the XML is valid (reading only some columns of a large CSV shows the dtype option on a related problem).

Names, encoding and the declaration

The column names become element names, so they must be legal XML names. A header like Customer Name or 2024 total needs the renaming treatment described in converting JSON to XML with sensible element names. Write the file with etree.tostring(root, xml_declaration=True, encoding="UTF-8") (as bytes), or tree.write(path, ...), so the declaration matches the bytes. If you write a Python string to a file opened in text mode, the platform's default encoding decides the bytes, and a declaration that says UTF-8 over bytes in another encoding is the mismatch error in encoding mismatch errors in XML documents.

Check the output

Parse what you wrote, from the file, with a parser that is not the one that wrote it. xmllint --noout rows.xml is enough for well-formedness, and xmllint --xpath 'count(//row)' rows.xml should equal the number of CSV data rows (minus rows rejected, which you logged). If a schema exists, validate against it. For a large CSV, write elements with etree.xmlfile, which streams the output so you never hold the whole tree, and works with the same escaping guarantees.

Related reading

The CSV side of the same conversion is in converting CSV to JSON, XML and YAML and converting CSV to XML in short Python. XML's character rules are in the XML 1.0 specification, section 2.2, and the pandas function in the to_xml reference. The xmlfile interface is documented in the lxml serialization guide. To test an XPath on the result, use the XPath tester.

When a string template is acceptable

The values are controlled. Generating a config from a short list of constants that you wrote, with no user data, can use a string template. Anything that contains a name, a note or a title is not controlled.

You are generating a fragment for a test. A hand-written fixture with known values does not need the machinery, though a library is no more work.

The target is not XML after all. If the template produces HTML or another markup, the escaping rules differ (HTML escapes differently, and has its own element rules). Use that format's tooling.

Performance is the argument. Template strings are faster than a tree, but the difference rarely matters, and a streaming writer (xmlfile) gives you most of the speed with the safety.