avjoshi
IS-IT--Management
- May 12, 2003
- 221
Hi,
I have managed to successfully register xml schema and create the xml tables. I now need to load the data into these tables. I am now working on different scenarios of loading the XML data into these tables.
I successfully experimented with the SQL/PLSQL options as well as SQL*Loader. I am reluctant to try the C and JAVA based interfaces because, frankly that is a completely new field for me and will present a huge learning curve that I won’t be able to pull off in short time I need to put this application in production.
My scenario, I have about 250 XML tables which I’m going to populate from XML data files containing thousands of records. I need the lode process to be automatic such that I can do scheduled runs.
The problem I have is SQL*Loader, and I am strictly going by the example given in the Oracle documentation, is that it requires for me to put ‘0’ for every record in the file. Here’s what my sample control file looks like,
LOAD DATA
INFILE *
INTO TABLE "MCF_Custom_Form_Date_Rg" APPEND
xmltype(xmldata)
FIELDS
(
xmldata LOBFILE (CONSTANT sampleload1.xml) TERMINATED BY '[XML]'
)
BEGINDATA
0
0
0
This method works if I have finite number of records which I’ll need to count before hand and add a ‘0’ for each record. That’s not an optimal solution because I will not know the number of records before hand. Is there any way of changing this behavior?
I am kind of stumped. What are my other alternatives? I read some where about SAX load utility. Where can I find more documentation about it? Can I run automated loading programs using it?
Any help on this would be greatly appreciated.
Regards,
AVJ.
Anand
I have managed to successfully register xml schema and create the xml tables. I now need to load the data into these tables. I am now working on different scenarios of loading the XML data into these tables.
I successfully experimented with the SQL/PLSQL options as well as SQL*Loader. I am reluctant to try the C and JAVA based interfaces because, frankly that is a completely new field for me and will present a huge learning curve that I won’t be able to pull off in short time I need to put this application in production.
My scenario, I have about 250 XML tables which I’m going to populate from XML data files containing thousands of records. I need the lode process to be automatic such that I can do scheduled runs.
The problem I have is SQL*Loader, and I am strictly going by the example given in the Oracle documentation, is that it requires for me to put ‘0’ for every record in the file. Here’s what my sample control file looks like,
LOAD DATA
INFILE *
INTO TABLE "MCF_Custom_Form_Date_Rg" APPEND
xmltype(xmldata)
FIELDS
(
xmldata LOBFILE (CONSTANT sampleload1.xml) TERMINATED BY '[XML]'
)
BEGINDATA
0
0
0
This method works if I have finite number of records which I’ll need to count before hand and add a ‘0’ for each record. That’s not an optimal solution because I will not know the number of records before hand. Is there any way of changing this behavior?
I am kind of stumped. What are my other alternatives? I read some where about SAX load utility. Where can I find more documentation about it? Can I run automated loading programs using it?
Any help on this would be greatly appreciated.
Regards,
AVJ.
Anand