Hi All,

I have implemented the insertion of data using the following method but it's taking very long.
Once XML and XSL files are there in mapped 'Directory_Path'....

BEGIN
SELECT directory_name INTO v_directory_name
FROM all_directories
WHERE owner = 'SYS'
AND directory_name = 'Directory_Path';

END;

xmldata1 := XMLTYPE(BFILENAME(v_directory_name, 'temp_xml.xml'), nls_charset_id('UTF8'));


xmldata2 := XMLTYPE(BFILENAME(v_directory_name, 'temp_xml.xsl'), nls_charset_id('UTF8'));



--Open a new context, required for these procedures
v_context := DBMS_XMLSTORE.newContext('TEMP_XML');


v_rows := DBMS_XMLStore.insertXML(
v_context,
XMLType.transform(xmldata1, xmldata2));

-- Close the context
DBMS_XMLStore.closeContext(v_context);

END;


May be I am converting data from BFILE to XMLType. But XMLType.transform(xmldata1, xmldata2) accepts XMLType data only.
Or May be this approach where I am not giving the column names explicitly..




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

Earlier I was loading the transformed XML file(With ROWSET ROW Format) in the the 'Directory_Path' and loading the data as

DECLARE
insCtx DBMS_XMLSTORE.ctxType;
rows NUMBER;
v_directory_name VARCHAR2(300);
src_file BFILE ;


BEGIN
BEGIN
SELECT directory_name INTO v_directory_name
FROM all_directories
WHERE owner = 'SYS'
AND directory_name = 'Directory_Path';

END;



src_file := BFILENAME(v_directory_name, XMLFILE.xml');


insCtx := DBMS_XMLSTORE.newContext( 'TABLE_NAME'); -- Get saved context
DBMS_XMLSTORE.clearUpdateColumnList(insCtx); -- Clear the update settings


-- Set the columns to be updated as a list of values
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'COLUMN1');
DBMS_XMLSTORE.setUpdateColumn(insCtx, 'COLUMN2');



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

--------------------------------------------------------------------------------
-- Insert the doc.
rows := DBMS_XMLSTORE.insertXML(insCtx, xmltype(src_file,nls_charset_id('AL32UTF8')));
DBMS_OUTPUT.put_line(rows || ' rows inserted.');

-- Close the context
DBMS_XMLSTORE.closeContext(insCtx);
END;


This was happening in a fraction of a second.
But for thousands of XMLs we can't manually generate the Transformed XMLs even if there is a single type of XSL for all those.



AM I MISSING SOMETHING VERY ELEMENTARY OVER HERE???
(I didn't meant to shout by putting in CAPS but highlight it so that it's not missed..Apologies )


The new suggestion being given is PARSE XML FILE into PIPE DELIMITED FORMAT FILE and LOAD that data through SQL LOADER



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

Personally I don't think it would be better when Oracle is giving us an inbuilt feature.

PLEASE HELP....

How can I improve this. I really think I am taking some wrong step somewhere.....


Regards.....