How do I store XML data into a mysql database? I don't want foreign keys like crazy.

Question

If my XML data is very complex, is there a way I can store this in DB?

Answer 1

Yeah there is the field type called TEXT.

Answer 2

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.

Answer 3

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.