In SQL Server I frequently use "FOR XML AUTO", which allows something like "SELECT id FROM car FOR XML AUTO" to return "<car><id>1</id></car><car><id>2</id></car>". Is there a way to do this in MySQL?
What I want is to be able to store this XML in another table so I can have complete log of changes made in the database. The history table will be populated by triggers, and each row will be a denormalized representation of the row that changed; storing XML seems like a great way to change the definition of a table and not worry about messing up the log history. So what I need is a simple and straightforward way to have an string that can be inserted as a data value, and this is string must be the XML description of the row.
Thank you very much!
See http://www.mysqludf.org/lib_mysqludf_xql/index.php for the lib_mysqludf_xql library from the MySQL UDF Repository.
The library provides a set of functions that generate XML output similar to SQL/XML as seen in Oracle/SQL Server. There are download/build/install instructions on the page along with a description of the library and the various functions it provides.
These should be sufficient to be used from within a trigger as you were planning.