Export XML with only one MySQL request ?

Question

I want to export in XML format some data from 7 tables (MySQL database), and then I want to import in another database. And I have a update or insert rule for data.

I already have a SQL query retrieving all data, with JOINs on my 7 tables. But...when I try to put data in XML format, I reach a limit. My PHP loop can catch each row, but I would like to benefit from hierachical structure of the XML, and all I have are rows with the same data repeated.

It is better to query once and to construct the XML tree in PHP or to query each time I want access to a lower level ?

Answer 1

Check out http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html#option_mysql_xml

XML is very verbose, so for large datasets you will probably hit your PHP memory limit. It's better to do something like this directly with MySQL (although you can still query it through PHP)

Answer 2

adding details : With my ORM, I can retrieve joins, so I begin to work with my Propel tool, for retrieving jointed tables.