[Sussex] Extracting data from MySQL into XML format

[John Crowhurst]

On Sun, July 10, 2005 8:36, Steve Dobson said:
> Hi guys
>
> Does anyone know of a tool that will extract data from a MySQL
> database and wrap it in XML.  I need to provide a "select" query
> to reduce the data going into the XML file, and of course it needs
> to be command line callable so I can script it.

Would perl be an option here?

You could use XML::Generator::DBI to read from your MySQL database and
wrap it into XML from your select statement.

I know mysqldump can export as XML, you can specify both the database and
table, but it doesn't support select statements. You can supply a WHERE
statement but you can't use select to limit the fields you wish to export.

mysqldump --user=user --pass=pass --xml --where="id>2" dbname tablename

> I also, of course, need a tool that takes the XML and inserts it
> into another database.

Under perl you could use XML::XMLParser to read in the XML and extract the
fields from it, you'd then insert them into your table.

This page has an example of importing and exporting XML from MySQL:
http://www.kitebird.com/articles/mysql-xml.html

--
John