Win a copy of Rust Web Development this week in the Other Languages forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

Inserting XML to mysql

 
Ranch Hand
Posts: 89
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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)!
 
Bartender
Posts: 2660
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2660
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
author
Posts: 4281
34
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
 
reply
    Bookmark Topic Watch Topic
  • New Topic