SQL to XML Converter

SQL to XML: rows as elements

Some integrations still exchange data as XML: catalog imports, publishing workflows, older SOAP-era services. When the source data lives in a SQL seed file or dump, this sql to xml converter reads the INSERT INTO statements and writes every inserted row as an XML element whose children are the table's columns. No database connection is involved; the statements are parsed as text.

Example

INSERT INTO books (isbn, title, author, price) VALUES
  ('978-0-00-000001-1', 'Rock & Roll', 'D''Arcy', 12.00),
  ('978-0-00-000002-8', 'Night Trains', NULL, 9.5);

With Root element books and Array item element book:

<?xml version="1.0" encoding="UTF-8"?>
<books>
  <book>
    <isbn>978-0-00-000001-1</isbn>
    <title>Rock &amp; Roll</title>
    <author>D&apos;Arcy</author>
    <price>12</price>
  </book>
  <book>
    <isbn>978-0-00-000002-8</isbn>
    <title>Night Trains</title>
    <author/>
    <price>9.5</price>
  </book>
</books>

The SQL escape '' became an apostrophe, which the XML writer then escapes as '; the ampersand becomes &. Both are standard XML entities and read back as the original characters.

Mapping rules

• NULL becomes an empty self-closing element such as <author/>. XML has no null, so NULL and an empty string look the same in the output. If the difference matters, convert with SQL to JSON instead.
• Numbers are written in their shortest form, so 12.00 becomes 12.
• Column names become element names. Characters that are not allowed in XML names are replaced with underscores, and a column whose name begins with the Attribute prefix (@ by default) becomes an attribute of the row element.
• Several tables in the input are placed side by side under the root, and each row element takes the name of its table (for example repeated <orders> and <tags> elements). The item name is used only when there is a single table.

Input tips

The parser accepts multi-row VALUES lists, quoted identifiers in backticks, double quotes or brackets, and comments. INSERTs without a column list take their column names from a CREATE TABLE in the same input. Queries such as SELECT are not converted, because they contain no row data. Before sending the XML on, check it in the XML validator or reformat it with the XML formatter.

Getting INSERT statements with column names

The converter needs column names, either in each INSERT or from a CREATE TABLE in the same input. Both major databases can export data that way:

# MySQL / MariaDB: one INSERT per row, with column names
mysqldump --no-create-info --complete-insert --skip-extended-insert shop books > books.sql

# PostgreSQL: INSERT statements with column names instead of COPY
pg_dump --data-only --column-inserts -t books shop > books.sql

After converting, check the result before handing it to an importer. Paste it into the XPath tester and run count(/books/book) to confirm the number of rows, or /books/book[price > 10]/title to spot-check values.

Privacy

Conversion runs in your browser. Load URL and Save & Share are the only features that send anything to our server.