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)!
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.
Joined: Apr 06, 2008
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?
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).