I want to convert my entire MySQL Table into XML... Can anyone Help me out with a tutorial or code.
-
Do you want to do this once, or routinely through a web interface?acrosman– acrosman2009-07-16 20:51:55 +00:00Commented Jul 16, 2009 at 20:51
-
use phpMyAdmin to export in various formats including XMLAlex L– Alex L2009-07-16 20:56:59 +00:00Commented Jul 16, 2009 at 20:56
Add a comment
|
5 Answers
mysqldump is able to dump data in XML format ; so, with something like this, you might get what you want :
mysqldump --user=USER --password=PASSWORD --host=HOST --xml DATABASE TABLE
For more information, see mysqldump
2 Comments
Kevin
What if i need it automatically each and every time
Pascal MARTIN
each and every time... that what occurs ? An insertion / update / deletion ? Well... If your table is big, you should probably not use this too often ^^ Can't you get the dump generated, say, once every day (or hour), via a cron job ? If the table is small and you need this xml dump always up to date... maybe a solution not based on mysqldump (ie, based on code) would be better...
For a simple highscore list for an iPhone game I wrote a small php thingy...its output can be viewed here
<highscores>
<?php
// connection information
$hostName = "xxxx";
$userName = "xxxx";
$password = "xxxx";
$dbName = "xxxx";
mysql_connect($hostName, $userName, $password)
or die("Unable to connect to host $hostName");
mysql_select_db($dbName) or die( "Unable to select database $dbName");
$query = "SELECT * FROM HIGHSCORES ORDER BY Distance, Fuel DESC LIMIT 10";
$result = mysql_query($query);
$number = mysql_numrows($result);
for ($i=0; $i<$number; $i++) {
print " <entry>\n";
printf(" <Num>%d</Num>\n", $i+1);;
$Nic = mysql_result($result, $i, "Nic");
print " <Nic>$Nic</Nic>\n";
$Distance = mysql_result($result, $i, "Distance");
printf( " <Distance>%.9f</Distance>\n", $Distance);
$Fuel = mysql_result($result, $i, "Fuel");
printf( " <Fuel>%.9f</Fuel>\n", $Fuel);
print " </entry>\n";
}
mysql_close();
?>
</highscores>
4 Comments
Kevin
But i am not able to construe the $nic or distance... how will actually call my field names with attributes.
epatel
You want it to create XML entries automatically without knowing the table structure? This adds another layer of complexity...I know my table and output only what I need, the table is actually bigger and holds more information.
Kevin
I need a to create XML entries for my field names with values.
epatel
"Nic" and "Distance" are fields in the table. $Nic and $Distance are variables that holds an entries values for these fields. They are then printed (outputted) in a XML format...
mysql outputs an XML document if you use -X or --xml option:
mysql -X -e "SELECT * FROM mytable" dbname
Comments
The mysql documentation has a page on using XML with MySQL. It would be easy to use that method with a system call to get the data already as XML and save yourself some trouble.
Comments
I haven't used the feature, but XMLSpy can create a schema from the database structure. It will also convert data or map it to XML based on an existing DTD or schema.
