Best method to load 50Mb xml file into an Oracle table to search on?

What is the best method (binary, lob, clob, external table, etc) to load this 50Mb xml file into an Oracle table to search on?

I've been reading through the Oracle XML DB Developer's Guide11g Release 2 (11.2). I've tried various methods of loading a very large 50mb XML file of XMLTYPE  into a table (CLOB, LOB, varchar2, etc) so I can perform a SQL query on the table to load Latitude and longitude values into an Oracle geospatial procedure I have set up.  The next method I want to try is Binary XML Storage,

create table XMLBIN of XMLType
XMLTYPE store as BINARY XML;

but I wanted to check here first since I've spend a lot of time on this already and I don't want to damage our heavily used test db.

I'm trying to use sqlldr to load a 50 mb xml file into an Oracle table. For each method I use, the locally managed tablespace grows over 10GB before stopping after 300 records loaded.  The error messages vary between method used but they all consume of 10GB of tablespace before stopping.  I had 16GB allocated but this seems like way too much memory being used.



CREATE TABLE TABLE_XML_DATA
(
  COL_XML_DATA  XMLTYPE
)
TABLESPACE ASM_LIMIT_TS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

---------------

sqlldr control file,

load data
infile 'c:\Geospat.xml'
into table TEST_USER.TABLE_XML_DATA
REPLACE
FIELDS TERMINATED BY ','
(
COL_XML_DATA lobfile (CONSTANT' c:\Geospat.xml' ) terminated by eof
)
talahiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I would first try BFILE and parse the XML from there.

Then I would probably load the XML file into a temp table using dbms_lob.loadclobfromfile:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lob.htm#i998978

Once you can see the XML, I would use XMLTABLE to parse it.  The extract and extractvalue functions are deprecated and not very efficient.  XMLTABLE seems to be pretty decent.
0
talahiAuthor Commented:
Should I use a locally managed tablespace and what size memory would I expect it to use to store this in an XMLTABLE?
0
slightwv (䄆 Netminder) Commented:
XMLTABLE is a function that parses an XMLTYPE data type and returns data as a 'normal' table.

When storing the file in an XMLTYPE data type locally or dictionary managed really shouldn't matter.

As far as the amount of memory to provide for the SGA, I really can't say.  I would start with whatever you have and see if Oracle complains that it cannot allocate more memory.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

talahiAuthor Commented:
ok thanks will give it a try.
0
slightwv (䄆 Netminder) Commented:
Don't you want to leave this open until you have a working solution?

If it doesn't work, you'll end up with a duplicate question.
0
talahiAuthor Commented:
I won't get back to this until later and by then there might be others responding.  I don't want to waste anyone's time while I'm busy on other things.  You've given me enough to keep busy with for now and it confirms some things I've thought about.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.