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");
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
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.
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.
Brian
thanks, it’s helpful
Bhavesh
very helpful article…..
it’s completely without any error..
admin
Thanks Bhavesh!
anusha
thanks a lot for this… I am to generate it for my website… Thanks again
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
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;
?>
kavati
how to save the xml file in a folder after generating it from database
MightyWebdev
Please see my response to Thet Thet’s comment.
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.
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.
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
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!
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.
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?
Tom Clive
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.
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 ??
MightyWebdev
Please see my response to Thet Thet’s comment.
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?
JAsh Parekh
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. */
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
MightyWebdev
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
Thanks but no runs
Androider
Yes yes runs, sorry, it was my issue
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.
sofia
Thank you! great tutorial!
A question:
How can I change de element names of my xml output?
fox
Thanks a lot 🙂
Trav
I have an xsl sheet for formatting output. Is there any way to implement that into the xml that is being created?
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
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.
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.
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
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……..
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,
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.
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?
MightyWebdev
Hi Jeff, try it out. In PHP variables in a string in quotation marks are replaced by the value of that variable.
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
Kendo
Nice script. But how to save the xml view in xml files in server ?
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 .
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.
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
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