Yes, SQL*Loader can load the file into an XMLType table or column, but it won't parse it for you.
You'll have to implement a second step to parse the input XML document and perform the necessary DML operations on your relational tables.
To sum up, I would :
- create a staging XMLType table
- Load the file into that table using SQL*Loader
- Parse the XML using XQuery (XMLTable, XMLQuery, XMLExists functions)
You'll find tons of examples of the last step in this forum.
Thanks for your reply ,
- Please would you quote an Example about : 'Load the file into that table using SQL*Loader' (From UNIX Server) Or instance of some existing thread that relates to my situation.
- The Size of the File would be about 3 GB. For a similar requirement one of my peers Code which used XMLTABLE and XPATH Approach consumed a lot of resources while running and caused the other Database Applications to slow down. Thus those guys have come up with an approach to :
Parse XML using a C Code using some STRING Functions => For a CSV or Fixed width .dat file and then use SQL Loader to just load the file into Tables.
This approach is efficient in terms of Resources and Time(Takes 5 mins). But I am not confident about parsing XML based on String based C Functions.
Please comment about this approach . Also if possible Suggest the best efficient way of doing this.
The Size of the File would be about 3 GB. For a similar requirement one of my peers Code which used XMLTABLE and XPATH Approach consumed a lot of resources while running and caused the other Database Applications to slow down.
That's a different story then.
Did they try with a temporary Binary XML storage ?
Schema-based structured storage is probably the best option in this case.
See the FAQ for some pointers : XML DB FAQ
We'll further advise if you think you can use this approach.
Have look at Client Side Encoding, that is encode on the client the document already in Binary XML (Using Binary XML for C) .
There is no need for "Parse XML using a C Code using some STRING Functions". Oracle provides the XDK (XML Development Kit) package for handling in Java, C or C++
Thanks for the replies mates !!!
It did help me learn lot of options.
But the simplest using UNIX server that worked out for me was to use an XSLT to convert the XML file into a delimited file.(Load the unix server, save Oracle resources).
UNIX functionality 'xsltproc' was not a bad option.
Then use SQL Loader to load into tables.