{"id":5,"date":"2004-10-29T20:47:23","date_gmt":"2004-10-29T20:47:23","guid":{"rendered":"http:\/\/gaisan.com\/wordp\/?p=5"},"modified":"2004-10-29T20:47:23","modified_gmt":"2004-10-29T20:47:23","slug":"mysql-xml-output","status":"publish","type":"post","link":"https:\/\/gaisan.com\/blogs\/?p=5","title":{"rendered":"MySql & XML Output"},"content":{"rendered":"

Pulled this from DECAFBAD<\/a>… Nice tasty article all about -X<\/code> command line switch in mysql. Wonderful I thought. XML based power in the worlds most popular and free<\/b> relational database. I heard the sounds of dreams coming true.. well maybe \ud83d\ude09 There had to be a catch however.
\n
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.<\/a>
\nIt 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???<\/b> Mysql only encloses the query results in XML element tags, but doesn’t do encoding of the contents inside the tags.
\n
\nIn 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<&#47foo>” with the -X command line option will always lead to invalid XML.
\nAn uncool workaround would be to perform some string replacements for every selected column when using the -X option:<\/p>\n