Mysql - Select To XML

Question

I'm using a trigger to capture an audit trail. I want to convert the inserted / updated / deleted value to XML and store it in another table.

My trigger look like this:

DELIMITER $$
CREATE
 TRIGGER `MyDB`.`TriggerAudit` AFTER INSERT
 ON `MyDB`.`settings`
 FOR EACH ROW BEGIN
 INSERT INTO insert_audit_trail (user_uid, table_name, inserted_value) 
 VALUES ('the Username', 'the table name', 'Select to XML In Here')
 END$$
DELIMITER ;

In MS SQL Server I can use the FOR XML clause, but I don't know to do this in MySQL. Is there any solution for this?

Answer 1

It appears this has already been answered here.

The answer references this article.

From the answer:

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 ();

• reptildarat: Yes, but its not a MySQL Query. any solution to integrate that into triggers or store procedures?
• Thomas Cleberg: If you already know the xml schema or can dynamically build it in the procedure, you can use UpdateXML - a description of which is detailed here.
• Thomas Cleberg: If you'd like, I could help with using the function. Could you please provide an example of a desired XML result and source data?