What is the equivalent of "FOR XML AUTO" in MySQL? (for having an XML string that represents a row)

Question

Hello!
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!
Regards,

Pedro

Answer 1

See http://eriksdiary.blogspot.com/2007/07/xml-output-from-mysql.html

for a way to do this in mysql. It's a workaround... and not terribly straightforward but afaik mysql doesn't have an easy way to do this

Also see: http://www.redips.net/php/from-mysql-to-xml/ if you're planning on doing this with php (much simpler method)

Answer 2

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.

-Dipin

Answer 3

There is no exactly same clause, but you can easily emulate it using:

SELECT  GROUP_CONCAT(CONCAT('<id>', id, '</id>' SEPARATOR ''))
FROM    car