On 10.2, I think the best option is to use Object-Relational storage.
SELECT * FROM v$version;
1. The file size would range 1-2 GB (and can go beyond) Is SQLLoader also an option feasible in this scenario?Yes, you can use SQL*Loader too, but it's likely you'll achieve a better throughput with the FTP approach.
2. The link you mentioned has this line: Storing the XML document in Oracle Database using Binary File (BFILE) -- How is this different from Binary XML in 11g? I am confused.Those are two different, unrelated things.
3. With respect to storage space usage, should we be concerned about the storage space/ memory that the operation of loading the file in this (temporary) table will take? Because right now, the xml feed file is residing in the webserver where java code iterates through it and inserts each record in the table. Now this will change and technically this file will have to go in the DB box.What method are you currently using BTW? SAX parsing?
We do not have the xsd for it as right nowOK.
"There are two reasons why the content of a Schema Based XML document is not accessable via deafult table. The first is that the document is protected with an ACL that forbids the user searching for the document from seeing it. The second is that the document was not recognized as being a member of the class defined by the XML Schema and so was stored as non-schema-based in the XML DB repository rather than as schema based XML in the default table."You're probably in the 2nd scenario here, i.e. the XML document is not schema-based.
<Engagements xmlns="com/.../edcdatatypes" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="com/.../edcdatatypes edcdatatypes.xsd">
declare res boolean; begin res := dbms_xdb.createResource( abspath => '/public/test/engagementData.xml' , data => xmltype(bfilename('TEST_DIR','engagementData.xml'), nls_charset_id('WE8MSWIN1252')) , schemaurl => 'edcdatatypes.xsd' , elem => 'Engagements' ); end; /
SELECT x.* FROM engagement_objects t , XMLTable( XMLNamespaces(default 'com/.../edcdatatypes') , '/Engagements/Engagement' passing t.object_value columns HostProfitCenter varchar2(30) path 'ProfitCenterData/HostProfitCenterData/HostProfitCenter' , ... ) x ;