Pulled this from DECAFBAD… Nice tasty article all about -X command line switch in mysql. Wonderful I thought. XML based power in the worlds most popular and free relational database. I heard the sounds of dreams coming true.. well maybe 😉 There had to be a catch however.
However, just when I thought everything was peachy I started playing with this feature with less than spectacular results. Some intensive googling yielded the answer.
It seems that using the -X command line option for exporting the data in XML format produces invalid XML. It assumes XML escaped data in the DB!.. On what grounds??? Mysql only encloses the query results in XML element tags, but doesn’t do encoding of the contents inside the tags.
In XML, if you want to use one of the characters <, >, &, etc. inside an element tag is not valid. If you want to use one of those characters, you have to use the respective entity instead. Mysql doesn’t seem to do that, so when selecting tagged data or markup like “<foo>red & green</foo>” with the -X command line option will always lead to invalid XML.
An uncool workaround would be to perform some string replacements for every selected column when using the -X option:
- replace all & by &
- replace all < by <
- replace all > by >
- replace all " by "
- replace all ' by '
Other stuff, like language specific characters (umlauts etc.) has to be encoded as well or has to be handled by defining or applying a different character set when post processing the XML output.
So the command produces invalid XML as invalid chars haven’t been escaped… Now this is a shame as writing some code to escape it in the db server coulda been done quite easily. A combination of escapes and using different charsets (perhaps as a command line option) along the lines of mysql --xml --xmlcharset=mycharset would be sweet. We’ll see what happens in the next release