Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Inserting XML to mysql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Inserting XML to mysql" Watch "Inserting XML to mysql" New topic
Author

Inserting XML to mysql

Jordan Smith
Ranch Hand

Joined: Apr 06, 2008
Posts: 89
HI!
my code, gets xml files and needs to insert the data into the database.
is there a difference if i use load xml or simply insert statements?
as well, how do i use load xml (i have just heard about it, but couldn't find a proper example except for the docs which was not clear)!
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Hi Jordan,

Do you need to store the complete XML file as a whole in the database, or
do you have to read data from the XML file, and insert its contents in separate tables/columns?

Regards, Jan

(typo fixed: I wrote"a whole" in stead of "as a whole")


OCUP UML fundamental and ITIL foundation
youtube channel
Jordan Smith
Ranch Hand

Joined: Apr 06, 2008
Posts: 89
I need to save the complete XML as it is (except for the header for sure)

as well, the names of the columns are not the same in the xml and in the database
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Jordan Smith wrote:I need to save the complete XML as it is (except for the header for sure)

as well, the names of the columns are not the same in the xml and in the database
If you have to insert the XML as one big chunk (I think this does not apply to you, because you want to remove headers before inserting), you can load it into a LOB (large object) field.

In case you haver to do limited transformation, it might be possible to use your database XML loader utility (several database brands offer such a tool).

If more logic is required, you will have to turn to more sophisticated ETL tools (specialized utilities that help you to define and run extract - transform - load),
or you will have to write a program that retrieves the data from XML, structures that data as needed for your data model, and inserts the data into the database.

Regards, Jan
Jordan Smith
Ranch Hand

Joined: Apr 06, 2008
Posts: 89
Hi
I saw somewhere that there is a command load xml. but, i didn't get how to use it.
as well, is it quicker than inserting each row alone?
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

I recommend storing it as text (VARCHAR) if its small enough. You need to use a LOB/CLOB/BLOB large object format otherwise. Some systems like Oracle actually have an XML type on top of a CLOB, but I found using it was never that useful unless you're querying on the XML a lot (in that case, though, you should store the fields you need in the database row).


My Blog: Down Home Country Coding with Scott Selikoff
 
Don't get me started about those stupid light bulbs.
 
subject: Inserting XML to mysql
 
Similar Threads
saving entity with a lazy property
ant & properties files
inserting data from XML to database
Programmatically inserting into a jar
Inserting values in an XML doc