Mysql – Select To XML

auditMySQLtriggerxml

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?

Best Answer

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