MySQL to XML

Question

I want to convert my entire MySQL Table into XML... Can anyone Help me out with a tutorial or code.

Answer 1

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.

Answer 2

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

Answer 3

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>

Answer 4

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.

Answer 5

mysql outputs an XML document if you use -X or --xml option:

mysql -X -e "SELECT * FROM mytable" dbname