Convert MySQL to XML using PHP

We start building the actual XML by creating the XML header and the necessary root element (which we derive from the table name, add “s” for plural!):

$xml          = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
$root_element = $config['table_name']."s"; //fruits
$xml         .= "<$root_element>";

And now for the actual fun stuff! We select the items from the table and use the mysql_fetch_assoc() function to return the query result as an associative array.

//select all items in table
$sql = "SELECT * FROM ".$config['table_name'];

$result = mysql_query($sql);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

if(mysql_num_rows($result)>0)
{
   while($result_array = mysql_fetch_assoc($result))
   {
      $xml .= "<".$config['table_name'].">";

      //loop through each key,value pair in row
      foreach($result_array as $key => $value)
      {
         //$key holds the table column name
         $xml .= "<$key>";

         //embed the SQL data in a CDATA element to avoid XML entity issues
         $xml .= "<![CDATA[$value]]>"; 

         //and close the element
         $xml .= "</$key>";
      }

      $xml.="</".$config['table_name'].">";
   }
}

That’s it! We have created a list of XML elements representing the MySQL data. Now all we have to do is properly close the XML and output the data to (in this case) the browser:

//close the root element
$xml .= "</$root_element>";

//send the xml header to the browser
header ("Content-Type:text/xml"); 

//output the XML data
echo $xml;
?>

The script can be easily modified to allow for different types of XML (think element names vs attributes). If the script is accessed through a URL we can make the script even more flexible by passing the table or column name(s) as a parameter. Always make sure that the SQL data doesn’t hold any (unicode) characters that are not allowed in XML! I will post a few more conversion examples in another post.

Please let me know if you have any questions or issues with the code using the comment form below.

Multi-page post:« Previous page
Page: 1 2

Leave a Reply

*

54 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

      • PHPNewb

        I don’t see the “example” for how to save to a file. I’ve tried several methods without success. Pls advise. Thanks

  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.

    • nakul

      please help my output is coming in CDATA. I want to remove that

      MY CODE IS:

      <?php

      $config['db_name'] = "algo_marketing";
      $config['table_name'] = "trade";

      //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");

      $xml = "”;
      $root_element = $config[‘table_name’].”s”;
      $xml .= “”;

      //select all items in table
      $sql = “SELECT * FROM “.$config[‘table_name’];

      $result = mysql_query($sql);
      if (!$result) {
      die(‘Invalid query: ‘ . mysql_error());
      }

      if(mysql_num_rows($result)>0)
      {
      while($result_array = mysql_fetch_assoc($result))
      {
      $xml .= “”;

      //loop through each key,value pair in row
      foreach($result_array as $key => $value)
      {
      //$key holds the table column name
      $xml .= “”;

      //embed the SQL data in a CDATA element to avoid XML entity issues
      $xml .= “”;

      //and close the element
      $xml .= “”;
      }

      $xml.=””;
      }
      }

      //close the root element
      $xml .= “”;

      //send the xml header to the browser
      header (“Content-Type:text/xml”);

      //output the XML data
      echo $xml;
      ?>

  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.

  26. James Flowers

    hi , this is fantastic , and works a treat (and your sensing a but here) BUT

    how can I trap
    ‘XML Parsing Error: not well-formed’
    It happens on a & that XML reads as an escape character?

    Many thanks, James

  27. john

    how to store the xml file to a specific folder,,kindly reply…….all about the code is very good…….it’s running absolutely without error……..

  28. ap3m4n

    Thanks for this code, worked well.
    however I just need to tweak to have some child elements, could you advice how to with your code.
    Thanks,

  29. Manikanta

    Hi Admin,

    Awesome tutorial. Its worked for me.Need your support for getting output in .xml format
    Example : i create a page called index.php i entered all the code it is working but i required in index.xml file as output.Please help me

    Thank you

    • Aamir

      Dear Reader,

      This helped me. Just put output into a variable then add this code.

      $myfile = fopen(“newfile.xml”, “w”) or die(“Unable to open file!”);
      fwrite($myfile, $xml);
      fclose($myfile);

      it will generate every time updated XML file when will refresh php.

  30. jeff

    Aren’t you improperly referencing the key and the value within the foreach construct since you have them contained inside the quotation marks? shouldn’t they be concatenated instead? If I reference a variable and have it in quotations, that’ not going to reference the variable, it will reference the actual text that you’ve written, correct?

    • Hi Jeff, try it out. In PHP variables in a string in quotation marks are replaced by the value of that variable.

  31. Muhammad Bilal

    Sir not work on google chrome and have this error

    “This XML file does not appear to have any style information associated with it. The document tree is shown below.”

    i am new in XML
    Help me

  32. Kendo

    Nice script. But how to save the xml view in xml files in server ?

  33. Lemosys

    It’s really knowledgeable post for programmer. Nicely explained by developer with example.currently i am working on oracle data base so ,it’s can applicable for other database like oracle,my access .

  34. Dipal Modi

    Hello,
    I have an error for following code in $xml .= “”;

    <?php

    //Database configuration
    $config['mysql_host'] = "localhost";
    $config['mysql_user'] = "root";
    $config['mysql_pass'] = "dipalmodi";
    $config['db_name'] = "xml";
    $config['table_name'] = "my_data";

    //connectto 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");

    $xml = "”;
    $root_element = $config[‘table_name’].”s”; //my_data
    $xml .= “”;

    //select all items in table
    $sql = “select * from “.$config[‘table_name’];

    $result = mysql_query($sql);
    if(!$result)
    {
    die(‘Invalid Query: ‘ .mysql_error());
    }

    if(mysql_num_rows($result)>0)
    {
    while($result_array = mysql_fetch_assoc($result))
    {
    $xml. = “”;

    //loop through each key, value pair in row
    foreach($result_array as $key => $value)
    {
    //$key holds the table column name
    $xml.=””;

    //embed the SQL data in a CDATA element to avoid XML entity issues
    $xml.= “”;

    //and close the element
    $xml.=””;
    }
    $xml.=””;
    }
    }

    //close the root element
    $xml.=””;

    //send the xml header to the browser
    header(“Content-Type:text/xml”);

    //output the xml data
    echo $xml;

    ?>

    please reply me ASAP.

  35. Adesh Shah

    Very good explanation, but it would be really great if you guys update the mysql command with PDO or mysqli. As I tried to run following example and it showed my that mysql is depreciated.

    Thanks,
    Adesh

  36. felix

    my code is
    <?php
    //database configuration
    $config['mysql_host'] = "localhost";
    $config['mysql_user'] = "root";
    $config['mysql_pass'] = "";
    $config['db_name'] = "mansion";
    $config['table_name'] = "property";

    //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");
    $xml = "”;
    $root_element = $config[‘table_name’].”s”;
    $xml .= “”;
    //select all items in table
    $sql = “SELECT * FROM “.$config[‘table_name’];

    $result = mysql_query($sql);
    if (!$result) {
    die(‘Invalid query: ‘ . mysql_error());
    }

    if(mysql_num_rows($result)>0)
    {
    while($result_array = mysql_fetch_assoc($result))
    {
    $xml .= “”;

    //loop through each key,value pair in row
    foreach($result_array as $key => $value)
    {
    //$key holds the table column name
    $xml .= “”;

    //embed the SQL data in a CDATA element to avoid XML entity issues
    $xml .= “”;

    //and close the element
    $xml .= “”;
    }

    $xml.=””;
    }
    }
    //close the root element
    $xml .= “”;

    //send the xml header to the browser
    header (“Content-Type:text/xml”);

    //output the XML data
    echo $xml;
    ?>

    Untitled Document

    when i run this code……I get the error message
    This page contains the following errors:

    error on line 1 at column 1418: error parsing attribute name
    Below is a rendering of the page up to the first error.
    plzz…….help…..its very urgent

Next ArticlePHP XPath Generator