Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

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

Tags:

database

data-structures

mysql

xml

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

like image 408
TIMEX Avatar asked Feb 05 '10 00:02

TIMEX

People also ask

Can you store XML in MySQL?

The most common way to store XML in MySQL is to use the LOAD_FILE() function to open an entire XML document, store it in a variable, and insert the variable into a table column.

Can you store XML in database?

"You can store XML in a database designed specifically for XML, in a modified object database, or in a relational database."

Should I store XML in database?

It's neither bad or good to do store XML in a DB. You just have to consider your requirements and how the data is used. If your data is machine produced and consumed, and it is only in XML to transport between apps, then a DB makes sense.

How do I import XML into MySQL?

To import data from a XML file into a MySQL table, select the table in Object Browser and select Table -> Import -> Import XML Data Using Load Local... or(Ctrl+Shift+X). Tables: The list of all tables of the currently active database is shown. Select the Table from the list box.

1 Answers

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.

like image 185
Pascal Thivent Avatar answered Oct 11 '22 22:10

Pascal Thivent
Sign in to Comment

Related questions

How to check if a table or a column exists in a database?
I dropped general_log table, how do I create it again?
SELECT row by DATEPART()
Where else can the variable innodb_buffer_pool_size be accessed besides my.cnf?
comparing dates by month and year in mysql
Converting mysql TIME from 24 HR to AM/PM format
What is a good MySQL Database Schema tool? [closed]
Developer tools to directly access databases [closed]
how to select top 5 max values in mytable
select next/previous 10 rows in mysql
Change max_heap_table_size value?
Achieve hierarchy, Parent/Child Relationship in an effective and easy way
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails
JSON Search in laravel eloquent
Storing UUID as string in mysql using JPA
How to measure current load of MySQL server?
Mysql: Which to use when: drop table, truncate table, delete from table
MySql using correct syntax for the over clause
MySQL Stored procedure variables from SELECT statements
Bash script to insert values in MySQL

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!