Convert MySQL to XML using PHP

How to  convert any MySQL table and data to a well-formed XML document using PHP. Building a  generic solution to a common problem.

In this tutorial we will create a standardized script that will convert a MySQL table of any shape or size into a valid XML document. This can be useful for any situation where you have to transfer data from a MySQL database into an XML based system. In some cases the “mysqldump –xml” might be sufficient. If you require output that’s different from the mysqldump format, or if you don’t have access to the mysqldump tool you will need to code your own solution. Creating a single-use script to handle specific data is of course quite easy, but would require custom coding for each table that needs to be converted to XML. I’m going to show you how (using a handy PHP function) you can build a more generalized solution. This mechanism will allow you to convert a MySQL table to XML, but also to a PHP array, CSV file (even though MySQL already supports CSV natively) or any other required format.

Let’s start with an example table and some data:

Table name: fruit

+--------------+----------------+------------------+--------------+
| fruit_id     | fruit_name     | fruit_colour     | price_per_kg |
+--------------+----------------+------------------+--------------+
| 1            | Banana         | yellow           | 2,99         |
| 2            | Orange         | orange           | 2,45         |
| 3            | Strawberries   | red              | 4,99         |
+--------------+----------------+------------------+--------------+

After running our PHP script the resulting XML will look like this:

<?xml version="1.0" encoding="UTF-8"?>
<fruits>
   <fruit>
      <fruit_id>1</fruit_id>
      <fruit_name>Banana</fruit_name>
      <fruit_colour>yellow</fruit_colour>
      <price_per_kg>2,99</price_per_kg>
   </fruit>
   <fruit>
      <fruit_id>2</fruit_id>
      <fruit_name>Orange</fruit_name>
      <fruit_colour>orange</fruit_colour>
      <price_per_kg>2,45</price_per_kg>
   </fruit>
   <fruit>
      <fruit_id>3</fruit_id>
      <fruit_name>Strawberry</fruit_name>
      <fruit_colour>red</fruit_colour>
      <price_per_kg>4,99</price_per_kg>
   </fruit>
</fruits>

Pretty neat huh? ;-) So let’s get down to business and start by setting up our database connection:

<?php
//database configuration
$config['mysql_host'] = "localhost";
$config['mysql_user'] = "root";
$config['mysql_pass'] = "grape01";
$config['db_name']    = "fruit_store";
$config['table_name'] = "fruit";

//connect to host
mysql_connect($config['mysql_host'],$config['mysql_user'],$config['mysql_pass']);
//select database
@mysql_select_db($config['db_name']) or die( "Unable to select database");
Multi-page post: Next page »
Page: 1 2

Leave a Reply

*

* Copy This Password *

* Type Or Paste Password Here *

39 comments

  1. Thet Thet

    This tutorial is very good for me to understand. But my need is not ‘only showing data on the browser’.
    I need to write an urgent advanced code using the xml file with php and mySQL database.
    How to produce physical xml file (not on the browser) from multiple tables?
    How to read data from this physical xml file and save in the multiple tables?
    Please kindly help me.

    • admin

      Hi Thet Thet,
      in the example the generated XML is stored in the $xml variable before being sent to the browser. If you wanted to store the XML to a file instead you can use the standard PHP functions for file handling. Example:

      To store data from an XML file into a database is a whole different story. Perhaps I’ll write another tutorial about that later. If you simply need to copy a database you’re better off using the mysqldump utility that comes with MySQL.
      Hope this helps!

      • harshi

        I tried writing the xml output into a file instead of echo.
        It shows an error saying error on line 1 at column 486: Extra content at the end of the document

  2. karen

    what to do if i need the php file to be dynamic? example it is sqlcovertxml.php..
    i want that file to accommodate any SELECT statement. how?

    • admin

      Hi Karen,
      how do you want the script to be dynamic? You could use a form to send a custom SQL statement to the script instead of predefining the query in the code.
      The SQL statement can be adjusted into whatever you like as long as we get an associative array with (key,value) pairs as a result. It gets tricky if you want to join two or more tables, you’ll probably want to adjust the names of the root element and that of the main element of each result row in that case.
      Hope this helps.

  3. charles

    i wish to generate xml from 3 tables.The resulting file should look something like

    how do i go about this?

    • admin

      You cannot post XML in the comments. Send me a sample using the contact form.

  4. very helpful article…..
    it’s completely without any error..

  5. anusha

    thanks a lot for this… I am to generate it for my website… Thanks again

  6. James

    Hi All.

    What a very simple, easy to follow, well written tutorial.

    Very good.

    Sorry, I’m having a little trouble.

    I would like my code to appear just like yours! Unfortunaely, it looks different, and includes the following

    Id there a way I can remove the CDATE just to display the following

    Kind regards

  7. admin

    Hi James, it looks like your code examples got filtered out. Can you try again? Or send me your full message including the code samples using the contact form.

  8. kavati

    how to save the xml file in a folder after generating it from database

  9. francess

    Hi,
    thank you, very helpfull.
    Please juste one question, I don’t have a probleme but just would like to know, for the appearing inside the file .xml (i choose this option)how to down the line.

    thanks again.

  10. rp

    Hi …,first of all not write $result ,how to check the condition. if (!$result = mysql_query($sql))
    die(“Query failed.”);

    • admin

      I altered the example somewhat to make it more readible.

  11. Rob

    This is giving me an error… “error on line 3 at column 6: XML declaration allowed only at the start of the document”

    when I delete the ‘.’ before the = in that line, it works, but I only get the first entry…

    Any ideas? Thanks

  12. Almir Campos

    I was looking for a tutorial to read the contents from a table and lists them in XML format. This was the only one that worked right off the bat. Thank you!

  13. Phil

    Hi,

    I’m a newbie, can you give me the complete script as a php file?
    I tried cop-pasting the above code, but something seems wrong, the script just does not execute.

  14. arjun

    I am getting error messages:
    Forefox: XML Parsing Error: junk after document element
    Line Number 1, Column 416:
    Google Chrome: This page contains the following errors:

    error on line 1 at column 414: Extra content at the end of the document
    Below is a rendering of the page up to the first error.

    How to solve those problems?

  15. Thanks for this and it’s something I should really know already. I’ve been doing a quick Google search on how to do this and your tutorial is one of the best explained I’ve come across.

    I modified the select * from to only get certain fields and it worked.

    If I look at the XML in Safari I get the [CDATA[ displaying too but it looks fine in Firefox. It’s definitely helping me out.

  16. john

    hello very nice tutorial! but i need to save the data in xml file not only to show in browser. how to do this ??

  17. Eeltje

    Hi admin,
    Great tutorial. It really helped me.
    In your answer to Thet Thet you mentioned: “Perhaps I’ll write another tutorial about that later.”.
    Did you find any opportunity to write this additional tutorial? If so, where can I find it?

  18. Hey I tried using your code, but gives me an error during runtime, shown below

    /* This page contains the following errors:

    error on line 1 at column 4433: Encoding error
    Below is a rendering of the page up to the first error. */

  19. Androider

    Hi! Nice tutorial, it´s very useful! I am novice and I have one question:

    If I only want the first 5 registers of the table ¿how can I do the select?

    $sql = “SELECT * FROM “.$config['table_name']+” LIMIT 5″;

    but it doesn´t work :-(

    Thank you

    • Looks like you have an error in your PHP syntax. In PHP a dot ( . ) is used for concatenating strings. The correct statement would be:

      $sql = “SELECT * FROM “.$config['table_name'].” LIMIT 5″;

      • Androider

        Yes yes runs, sorry, it was my issue

  20. khizar

    Hello this is was really good 1 for who new on xml, It help me lot thanks once again.
    Still I need this generated xml to server folder. ?? Plz need solution for this.

  21. sofia

    Thank you! great tutorial!

    A question:
    How can I change de element names of my xml output?

  22. Trav

    I have an xsl sheet for formatting output. Is there any way to implement that into the xml that is being created?

  23. alexandyducu

    Why is needed to use CDATA? …. “$xml .= “”;”
    I use simple : “$xml.=$value;” and everything is ok!

    In other news, your tutorial is super!! BRAVISIMO

  24. xjshiya

    Hello,

    Thanks for this awesome tutorial! It helped me a lot. But I have a little problem. What if I don’t want to ‘echo’ the generated XML? Instead I want to download it as an XML file? I know it has something to do with the ‘header’ part. I’ve tried:

    header(“Content-type: application/octet-stream”);
    header(“Content-Disposition: attachment; filename=test.xml”);
    header(“Pragma: no-cache”);
    header(“Expires: 0″);

    But the XML file doesn’t look the same as the echoed one. Can you help? Thanks.

  25. lovcrimson

    Thanks for sharing ur experience. I’v tried ur code and it works mostly fine for me except the annoying encoding problem.
    My code goes like this, adapted from urs, to export ‘Xml’ key of every item as a new xml:

    while($result_array = mysql_fetch_assoc($result))
    {
    foreach($result_array as $key => $value)
    {
    $xml = “”;
    $xml .= $result_array['Xml'];
    }
    $new_xml = “..\\export\\”.$result_array['CardID'].”.xml”;
    $file_handle = fopen($new_xml, “w”);
    fwrite($file_handle, $xml);
    fclose($file_handle);
    }

    It works but all special characters are missing. MySQL charset is UTF-8 Unicode (utf8), MySQL connection collation is utf8_general_ci, the encoding of generated xml files are indeed utf-8. I dont understand what’s going wrong here and being stuck for several days, would you please give me a hand?

    • lovcrimson

      Oh I just found the solution after I made that post…

      Simply adds
      mysql_query(“set names ‘utf8′”);
      and then everything is going well.

Next ArticlePHP XPath Generator