XML output from MySQL

Question

Hi,

is there any chance of getting the output from a MySQL query directly to XML?

Im referring to something like MSSQL has with SQL-XML plugin, for example:

SELECT * FROM table WHERE 1 FOR XML AUTO

returns text (or xml data type in MSSQL to be precise) which contains an XML markup structure generated according to the columns in the table.

With SQL-XML there is also an option of explicitly defining the output XML structure like this:

SELECT
  1       AS tag,
  NULL    AS parent,
  emp_id  AS [employee!1!emp_id],
  cust_id    AS [customer!2!cust_id],
  region    AS [customer!2!region]
 FROM table
 FOR XML EXPLICIT

which generates an XML code as follows:

<employee emp_id='129'>
   <customer cust_id='107' region='Eastern'/>
</employee>

Do you have any clues how to achieve this in MySQL?

Thanks in advance for your answers.

Answer 1

Using XML with MySQL seems to be a good place to start with various different ways to get from MySQL query to XML.

From the article:

   use strict;
   use DBI;
   use XML::Generator::DBI;
   use XML::Handler::YAWriter;

   my $dbh = DBI->connect ("DBI:mysql:test",
                           "testuser", "testpass",
                           { RaiseError => 1, PrintError => 0});
   my $out = XML::Handler::YAWriter->new (AsFile => "-");
   my $gen = XML::Generator::DBI->new (
                                   Handler => $out,
                                   dbh => $dbh
                               );
   $gen->execute ("SELECT name, category FROM animal");
   $dbh->disconnect ();

Answer 2

Check this out, too, it might help: http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html .