• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Inserting XML to mysql

 
Jordan Smith
Ranch Hand
Posts: 89
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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")
 
Jordan Smith
Ranch Hand
Posts: 89
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 89
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 3903
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic