SQL Data Export: Convert SQL to Multiple Formats

Exporting SQL data to various formats is common in reporting, data exchange, and integration. Learn how to convert SQL results to CSV, JSON, XML, YAML, and HTML.

SQL to CSV

Basic Export

-- MySQL
SELECT * FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
';

JavaScript Conversion

function sqlToCSV(sqlResults) {
  const headers = Object.keys(sqlResults[0]);
  const rows = sqlResults.map(row =>
    headers.map(h => JSON.stringify(row[h] ?? '')).join(',')
  );
  return [headers.join(','), ...rows].join('
');
}

SQL to JSON

Array of Objects

[
  {"id": 1, "name": "John", "age": 30},
  {"id": 2, "name": "Jane", "age": 25}
]

JavaScript Conversion

function sqlToJSON(sqlResults) {
  return JSON.stringify(sqlResults, null, 2);
}

SQL to XML

<?xml version="1.0"?>
<results>
  <row>
    <id>1</id>
    <name>John</name>
    <age>30</age>
  </row>
  <row>
    <id>2</id>
    <name>Jane</name>
    <age>25</age>
  </row>
</results>

JavaScript Conversion

function sqlToXML(sqlResults, rootName = 'results') {
  let xml = '<?xml version="1.0"?>
<' + rootName + '>
';

  for (const row of sqlResults) {
    xml += '  <row>
';
    for (const [key, value] of Object.entries(row)) {
      xml += `    <${key}>${value}</${key}>
`;
    }
    xml += '  </row>
';
  }

  xml += '</' + rootName + '>';
  return xml;
}

SQL to YAML

- id: 1
  name: John
  age: 30
- id: 2
  name: Jane
  age: 25

SQL to HTML

<table>
  <thead>
    <tr>
      <th>id</th>
      <th>name</th>
      <th>age</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>John</td>
      <td>30</td>
    </tr>
  </tbody>
</table>

JavaScript Conversion

function sqlToHTML(sqlResults) {
  if (!sqlResults.length) return '<table></table>';

  const headers = Object.keys(sqlResults[0]);

  let html = '<table>
<thead>
<tr>';
  html += headers.map(h => `<th>${h}</th>`).join('');
  html += '</tr>
</thead>
<tbody>
';

  for (const row of sqlResults) {
    html += '<tr>';
    html += headers.map(h => `<td>${row[h] ?? ''}</td>`).join('');
    html += '</tr>
';
  }

  html += '</tbody>
</table>';
  return html;
}

Use Cases

Format	Best For
CSV	Spreadsheets, imports
JSON	APIs, web apps
XML	Legacy systems, SOAP
YAML	Config files
HTML	Reports, display

Try Our SQL Export Tools

• SQL to CSV
• SQL to JSON
• SQL to XML
• SQL to YAML
• SQL to HTML

Conclusion

Different formats serve different needs. Choose based on your target system and use case.