An INSERT already has the shape XML wants
A table name, a column list, one tuple per row. That is a container, a set of field names and a set of records, which maps onto XML with nothing left over. The work sits in the details: a comma inside a quoted string must not split a row, a column called 2nd_line is not a legal element name, and a NULL is not the same thing as an empty string once a parser on the other end reads the file.
Read this first: only INSERT and REPLACE statements with literal value tuples are parsed. A CREATE TABLE block, an INSERT ... SELECT, or a Postgres COPY section is skipped without a word. If your file is mostly schema, run it through the SQL formatter first so you see what is left, then paste the INSERT sections here.
Three settings decide the whole document
The rail on the left is short on purpose. Everything else follows from these three answers.
Where rows live
The root element name is yours to set. Keep Wrap rows in a table node on and each table gets its own container inside the root. Turn it off and every row sits directly under the root, which suits a single table feeding an import job.
What a row is called
The default takes the table name and drops the plural, so orders gives <order> and catalog_items gives <catalog_item>. Pick the table name as written when a schema on the other side expects it, or a flat <row> when the consumer reads by position.
How a column is written
Child elements give one node per column and survive future nesting. Attributes flatten each row into a single self-closing tag, which cuts the file roughly in half on wide tables with short values.
Child elements or attributes
Both outputs are well-formed. The choice depends on who reads the file next. Here is the same row under each setting.
<order><id>5001</id><customer>Amara Okafor</customer><total>249.50</total><status>shipped</status></order><order id="5001"
customer="Amara Okafor"
total="249.50"
status="shipped"/>Elements win when values contain line breaks, when a field might later grow children of its own, or when a reviewer reads the diff of a fixture file in a pull request. Attributes win on size and on formats where a schema already declares every field as an attribute, such as older configuration loaders. A long free-text column inside an attribute turns into one enormous line with markers where the newlines were, so text bodies belong in elements.
What happens to NULL
SQL has three states for a text column: a value, an empty string, and NULL. XML has two by default, so a converter has to pick a convention and say which one it picked.
| Setting | Output for a NULL column | Pick it when |
|---|---|---|
| Empty element | <note/> | The reader treats missing and empty as the same thing, and you want a stable element count per row |
| xsi:nil | <note xsi:nil="true"/> | An XSD or a typed deserializer needs a real null, not an empty string. The namespace is declared on the root for you |
| Nothing at all | The element is left out of that row | File size matters on a sparse table, and the consumer handles absent fields |
Attributes have no equivalent of xsi:nil. Choosing it while attribute output is on writes an empty attribute value instead, and a note appears under the editors saying so. That is the one place the two settings pull against each other.
Names XML refuses to accept
Element names follow rules that column names do not. A name starts with a letter or an underscore, holds no spaces, and must not begin with the three letters xml in any casing. Real schemas break all three.
2nd_addressstarts with a digit, so it becomes_2nd_address.order totalandprice (usd)hold characters that are not allowed, so each one becomes an underscore.xml_payloadis reserved by the specification, so an underscore is added in front.
Every rewrite is listed in the parser notes below the editors, because a silently renamed field is the kind of thing you find out about three systems later. Table names keep their schema prefix, so shop.orders stays distinct from archive.orders and the two never merge into one node.
An INSERT with no column list
Dumps written by mysqldump --compact often drop the column names and rely on table order. The statement carries no names to use, so fields read column_1 through column_n based on the widest tuple, and a note tells you it happened. If an earlier INSERT for the same table did carry a column list, those names are reused instead, which covers the common case of one full statement followed by several short ones.
Indentation is not only cosmetic
Two spaces reads well in review. The single line option strips every newline and indent, which matters when the XML rides inside a request body or a database column and the whitespace would count against a size limit. On a ten thousand row export the difference runs to several megabytes. Re-indent later with the XML pretty printer when a human needs to read it again.
When XML is the wrong target
- Spreadsheets and quick scripts want CSV, which carries none of the tag overhead.
- Test fixtures and Rails or Symfony seed files read better as YAML.
- A table for a ticket or an internal doc is a job for SQL to HTML.
- Building the schema before any rows exist belongs in the SQL table generator, which writes CREATE TABLE rather than reading INSERT.
Limits worth knowing before you paste
- No type information. Every value is written as text. XML has no numeric type without a schema, so
249.50and"249.50"look identical on the wire. Attach an XSD if the consumer needs types. - No expression evaluation.
NOW(),UUID()and arithmetic arrive as the literal characters you typed, because nothing here connects to a database that would resolve them. - No binary decoding. A blob written as
0x89504E47stays that string. XML has no byte type, and re-encoding to base64 would be a guess at what you wanted. - Control characters are dropped by the spec. XML 1.0 forbids most characters below U+0020. A value carrying a raw NUL byte produces a document some strict parsers reject.
- Large dumps slow the tab. Parsing and rendering run on the main thread. A few megabytes is fine. A production-sized export belongs on the server with a proper tool.
Nothing you paste is uploaded. Parsing, conversion, copy and download all run in JavaScript on this page, so a dump holding customer rows never leaves your machine.
