how to insert xml into mysql from a script?

Question

Hi!! bit of a curly one here let me know if you have any ideas..

For various reasons I need to store a small number of medium size xml documents in mysql (yes I know that technically violates 1NF). I want to script this load so I can drop-recreate the database, and also for my release scripts, however the catch is that the prod release is via phpMyAdmin, so the usual route of running scripts on the server and LOADFILE is out.. :-(

My question is, my xml contains double-quotes ("), line breaks (\n) etc, is there a way I can embed this into a single statement that I can run in a web form like pMA?

the fallback position of course, is to insert dummy data, then manually update the rows with the xml, but I prefer to avoid extra manual steps where I don't have to.

any ideas?

Answer 1

never mind, turns out I can just embed the xml text into any standard insert/update statement!! I just have to escape single quotes.

Answer 2

PHPMyAdmin has support for uploading a file with SQL expressions and running it. Just mysqldump your database and upload it to PHPMyAdmin.

You can also check out the --hex-blob option for mysqldump. It will convert all BLOB fields to hexadecimal notation in the dump file, which will "hide" your special characters.