How do I store XML data into a mysql database? I don't want foreign keys like crazy
The "regular" way is to store XML in a CLOB (Character Large Object) and MySQL supports CLOB with 4 data types:
- TINYTEXT - A CLOB column with a maximum length of 255 (2**8 - 1) characters.
- TEXT - A CLOB column with a maximum length of 65,535 (2**16 - 1) characters.
- MEDIUMTEXT - A CLOB column with a maximum length of 16,777,215 (2**24 - 1) characters.
- LONGTEXT - A CLOB column with a maximum length of 4,294,967,295 or 4GB (2**32 - 1) characters.
Using one or the other depends on your needs.
It all depends on what you want your database to do with the XML.
- If you just want to store the XML document for later retrieval, just use a blob or text field. Also check the MySQL docs.
- If you are trying to dump/import a model, use mysqldump.
- If you are planning to query on XML, you should probably be using a native XML database such as eXist-db instead.
I would recommend using a database that has a native XML datatype. Postgres will do this, for example. This will make life much easier for you if you are planning to lots of work with XML in your database.