sqlldr failed while loading XML file

Here are just tips:
1) in ctl file: ...
,MIDDLE_INITIAL CHAR enclosed by "<MIDDLE_INITIAL>" and "</MIDDLE_INITIAL>" - here is only CHAR without lenght
2) also in xml file there is only <MIDDLE_INITIAL />, but in ctl file there is
... enclosed by "<MIDDLE_INITIAL>" and "</MIDDLE_INITIAL>"

thanks for the comment,
In my xml file <MIDDLE_INITIAL />,  just because of null value. when it have  value then it will enclosed with  .ctl fomat. you could see some  other fields are also in the same way. I would like to get how to solve this null value issue while loading

Using SQL*Loader to process XML is a neat trick but as Henka pointed out, can have problems.  Another issue you might run into is encoding.  In XML certain characters get special encoding.  Loading the data this way will not encode it properly.

It's best to use XML calls to load the data.

drop table tab1 purge;
create table tab1(first_name varchar2(100), last_name varchar2(100));

drop directory xml_dir;
create directory xml_dir as 'C:\';

CREATE OR REPLACE procedure load_xml
(
	v_file_name		IN	varchar2
)

IS

BEGIN

DECLARE

	v_bfile			 bfile := BFILENAME( 'XML_DIR' , v_file_name);
	v_lob			 clob;

	src_offset  number := 1;
	dst_offset  number := 1;
	cs_id       number := NLS_CHARSET_ID('UTF8'); /* 998 */
	lang_ctx    number := dbms_lob.default_lang_ctx;
	warning     number;

	BEGIN

	insert into bob(xml_clob )
		values(empty_clob()) returning xml_clob into v_lob;

	DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
	dbms_lob.createtemporary(v_lob,TRUE);
	dbms_lob.open(v_lob,dbms_lob.lob_readwrite);
	dbms_lob.LOADCLOBFROMFILE(v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile), dst_offset, src_offset, cs_id, lang_ctx,warning);
	DBMS_LOB.CLOSE(v_bfile);

	insert into tab1 values(
		extractvalue(xmltype(v_lob),'/PROVIDER/FIRST_NAME'),
		extractvalue(xmltype(v_lob),'/PROVIDER/LAST_NAME')
	);

	DBMS_LOB.FREETEMPORARY(v_lob);

	END;

END;
/

show errors

exec load_xml('xml.txt');
select * from tab1;

Select allOpen in new window

Do I need to copy this xml file on xml_dir, if so how can I

Yes.  To use this method the XML file needs to be on the database server.

If you have the file on a client machine I'm thinking the best way is to load the XML into a CLOB filed in a table using sql*loader.  Then we can modify the procedure I posted to get the CLOB from a table instead of from a file.

Yes I can do that. I can upload this document to CLOB by using sqlldr.

I've never done it but it looks possible.

Check out:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:627223659651

I tried the following procedureto get the xmldocument to prov_hmo table but Inserting null values


CREATE OR REPLACE PROCEDURE InsertXML_ADT
IS
   v_npi varchar2(10000);
   v_fname varchar2(10000);
   v_lname varchar2(10000);
   
   CURSOR getXML IS
    SELECT extract(xmldoc,'/Provider/NPI').getStringVal(),
           extract(xmldoc,'/Provider/First_Name').getStringVal(),
           extract(xmldoc,'/Provider/Last_Name').getStringVal()
    FROM PROV_TABLE;
Begin
OPEN getXML;
Loop
 fetch getXML into v_npi,v_fname,v_lname;
 Exit when getXML%NOTFOUND;
 
 insert into prov_hmo (npi, first_name,last_name)
 select v_npi,v_fname,v_lname from dual;
   
 commit;

End loop;

Close getXML;

commit;

End InsertXML_ADT;

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 13 14:56:19 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect xml/xml@test

SQL> CREATE TABLE empleados (empid   NUMBER PRIMARY KEY,
  2                          empname VARCHAR2(30),
  3                          empjob  VARCHAR2(30),
  4                          empsal  NUMBER);

Table created.

SQL> create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN VARCHAR2) is
  2     insCtx DBMS_XMLSave.ctxType;
  3     rows number;
  4   begin
  5      insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
  6      rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
  7      dbms_output.put_line(to_char(rows) || ' rows inserted');
  8      DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle
  9  end;
 10  /

Procedure created.
SQL> exec InsertXML('<?xml version="1.0"?><ROWSET><ROW num="1"><EMPID>10</EMPID><EMPNAME>Perry Smith
</EMPNAME><EMPJOB>Manager</EMPJOB><EMPSAL>800</EMPSAL></ROW><ROW num="1"><EMPID>20</EMPID><EMPNAME>J
ohn Calvach</EMPNAME><EMPJOB>Principal Support Consultant</EMPJOB><EMPSAL>900</EMPSAL></ROW><ROW num
="1"><EMPID>30</EMPID><EMPNAME>Louis Bald</EMPNAME><EMPJOB>Technical Specialist</EMPJOB><EMPSAL>400<
/EMPSAL></ROW><ROW num="1"><EMPID>40</EMPID><EMPNAME>Anthony Flowers</EMPNAME><EMPJOB>Technical Team
 Leader</EMPJOB><EMPSAL>500</EMPSAL></ROW><ROW num="1"><EMPID>50</EMPID><EMPNAME>George Monk</EMPNAM
E><EMPJOB>Support Consultant</EMPJOB><EMPSAL>200</EMPSAL></ROW></ROWSET>','empleados');

PL/SQL procedure successfully completed.

SQL> select * from empleados;

     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        10 Perry Smith                    Manager
       800

        20 John Calvach                   Principal Support Consultant
       900

        30 Louis Bald                     Technical Specialist
       400


     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        40 Anthony Flowers                Technical Team Leader
       500

        50 George Monk                    Support Consultant
       200


SQL>



SQL> select SYS_XMLGEN(empid) FROM empleados;

SYS_XMLGEN(EMPID)
---------------------------------------------------------
<?xml version="1.0"?>
<EMPID>10</EMPID>

<?xml version="1.0"?>
<EMPID>20</EMPID>

<?xml version="1.0"?>
<EMPID>30</EMPID>

<?xml version="1.0"?>
<EMPID>40</EMPID>

SYS_XMLGEN(EMPID)

<?xml version="1.0"?>
<EMPID>50</EMPID>


SQL> select SYS_XMLAGG(SYS_XMLGEN(empid)) FROM empleados;
<?xml version="1.0"?>
<ROWSET>
<EMPID>10</EMPID>
<EMPID>20</EMPID>
<EMPID>30</EM

>>I tried the following procedureto get the xmldocument to prov_hmo table but Inserting null values


XML is case sensitive.  You used '/Provider/NPI' and the XML you posted has 'PROVIDER/NPI'.  Same for LAST_NAME and FIRST_NAME.

I found the solution for this :
SELECT extract(xmldoc,'/Provider/NPI').getStringVal() from dual
will provide the text with XMLtags.
example: <NPI> ASUS</NPI>

SELECT extract(xmldoc,'/Provider/NPI/text()').getStringVal(),
from dual
Will provide the exact text between the tags;
Example : ASUS
Thanks for everybody who contribute the answers here an it help me find out this solution

Just because you chose to not use my suggestion does not mean I did not provide an answer.

Also the question involved a lot more than just extracting the XML text.  It also involved getting the data from disk into Oracle.

The example I posted in http:#a31860452 does what you asked for.

extractvalue and extract with text() do two different things.  One extracts the encoded text as-is.  The other decodes.

to see the difference run the following in sql*plus

set define off
select extractvalue(xmltype('<a>AT&amp;T is a phone company</a>'),'/a') from dual;
select extract(xmltype('<a>AT&amp;T is a phone company</a>'),'/a/text()').getstringval() from dual;
set define on

Select allOpen in new window

I could not follow the complete directions on it because I donot have privilages to create directory in oracle server.

>>I donot have privilages to create directory in oracle server.

So?  You don't need to have them.  That was just a nice work-around.  Use SQL*Loader to load the file into a CLOB ( http:#a31870816 ) then use the code I provided.  Just get rid of the temporaryCLOB and the loadclobfromfile code.  Everything else should work.

Okay agreed. But I do have XML file with around 15000 records that I need to insert into oracle table. For example  "First_name"  length of the field is 15, so the total size of  pl_sql variable is 225000 bytes but we can carry only 32627 in varchar2 field. In that case I will miss lot of records from XML file.

What wil be the good solution for this. I have touse fetch statement to get this value. because  of XML type document it fetch all the values in single variable. How we can retreive the value one by one from xmltype document

Okay agreed. But I do have XML file with around 15000 records that I need to insert into oracle table. For example  "First_name"  length of the field is 15, so the total size of  pl_sql variable is 225000 bytes but we can carry only 32627 in varchar2 field. In that case I will miss lot of records from XML file.

What wil be the good solution for this. I have touse fetch statement to get this value. because  of XML type document it fetch all the values in single variable. How we can retreive the value one by one from xmltype document

I'm afraid I don't understand.

Load the XML file into a CLOB table using SQL*Loader.  Once this is done, select the CLOB field into a PL/SQL CLOB variable then use the 'insert into' code I posted.  Just strip out the loadclobfromfile code.

What am I missing?

The code I posted in http:#a31860452 uses the XML you provided in the original question ( http:#a26016092 ).

drop table tab1 purge;
create table tab1(first_name varchar2(100), last_name varchar2(100));

drop table temp_clob purge;
create table temp_clob(col1 clob);

--simulate SQL*Loader call and just insert the test data
insert into temp_clob values('<PROVIDER>
   <NPI>1234567890</NPI>
   <FIRST_NAME>Pamela</FIRST_NAME>
   <LAST_NAME>Zielinski</LAST_NAME>
   <MIDDLE_INITIAL />
   <GENDER>M</GENDER>
   <LINE_1_ADDRESS>14013 Albert way</LINE_1_ADDRESS>  
   <LINE_2_ADDRESS />
   <CITY>manassas</CITY>
   <STATE>VA</STATE>
   <ZIP>22010</ZIP>
   <COUNTY>042</COUNTY>
   <PHONE_NUMBER>7158470067</PHONE_NUMBER>
   <CLINIC_NAME>Prince william Hospital</CLINIC_NAME>
   <ACCEPT_NEW_PATIENT>Y</ACCEPT_NEW_PATIENT>
   <PRIMARY_CARE_INDICATOR>N</PRIMARY_CARE_INDICATOR>
</PROVIDER>');

commit;



CREATE OR REPLACE procedure load_xml
IS

BEGIN

DECLARE

	v_lob			 clob;

	BEGIN

	select col1 into v_lob from temp_clob;

	insert into tab1 values(
		extractvalue(xmltype(v_lob),'/PROVIDER/FIRST_NAME'),
		extractvalue(xmltype(v_lob),'/PROVIDER/LAST_NAME')
	);

	END;

END;
/

show errors

exec load_xml;
select * from tab1;

Select allOpen in new window

I changed the xml file like follows
insert into temp_clob values('<PROVIDER>
   <NPI>1234567890</NPI>
   <FIRST_NAME>Pamela</FIRST_NAME>
   <LAST_NAME>Zielinski</LAST_NAME>
   <MIDDLE_INITIAL />
   <GENDER>M</GENDER>
   <LINE_1_ADDRESS>14013 Albert way</LINE_1_ADDRESS>  
   <LINE_2_ADDRESS />
   <CITY>manassas</CITY>
   <STATE>VA</STATE>
   <ZIP>22010</ZIP>
   <COUNTY>042</COUNTY>
   <PHONE_NUMBER>7158470067</PHONE_NUMBER>
   <CLINIC_NAME>Prince william Hospital</CLINIC_NAME>
   <ACCEPT_NEW_PATIENT>Y</ACCEPT_NEW_PATIENT>
   <PRIMARY_CARE_INDICATOR>N</PRIMARY_CARE_INDICATOR>
</PROVIDER>
<PROVIDER>
   <NPI>1234567890</NPI>
   <FIRST_NAME>Pam</FIRST_NAME>
   <LAST_NAME>Zoe</LAST_NAME>
   <MIDDLE_INITIAL />
   <GENDER>M</GENDER>
   <LINE_1_ADDRESS>14013 Albert way</LINE_1_ADDRESS>  
   <LINE_2_ADDRESS />
   <CITY>manassas</CITY>
   <STATE>VA</STATE>
   <ZIP>22010</ZIP>
   <COUNTY>042</COUNTY>
   <PHONE_NUMBER>7158470067</PHONE_NUMBER>
   <CLINIC_NAME>Prince william Hospital</CLINIC_NAME>
   <ACCEPT_NEW_PATIENT>Y</ACCEPT_NEW_PATIENT>
   <PRIMARY_CARE_INDICATOR>N</PRIMARY_CARE_INDICATOR>
</PROVIDER>');


I got he following eror while running the procedure;

Connecting to the database DSS.
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00245: extra data after end of document
Error at line 18
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1
ORA-06512: at "OZGB4K.LOAD_XML", line 14
ORA-06512: at line 2
Process exited.
Disconnecting from the database DSS.
I have more than 10000 records in XML file.

That XML isn't valid.  There is no 'root' node.

Add a root node and a little loop

drop table tab1 purge;
create table tab1(first_name varchar2(100), last_name varchar2(100));

drop table temp_clob purge;
create table temp_clob(col1 clob);

--simulate SQL*Loader call and just insert the test data
insert into temp_clob values('<root><PROVIDER>
   <NPI>1234567890</NPI>
   <FIRST_NAME>Pamela</FIRST_NAME>
   <LAST_NAME>Zielinski</LAST_NAME>
   <MIDDLE_INITIAL />
   <GENDER>M</GENDER>
   <LINE_1_ADDRESS>14013 Albert way</LINE_1_ADDRESS>  
   <LINE_2_ADDRESS />
   <CITY>manassas</CITY>
   <STATE>VA</STATE>
   <ZIP>22010</ZIP>
   <COUNTY>042</COUNTY>
   <PHONE_NUMBER>7158470067</PHONE_NUMBER>
   <CLINIC_NAME>Prince william Hospital</CLINIC_NAME>
   <ACCEPT_NEW_PATIENT>Y</ACCEPT_NEW_PATIENT>
   <PRIMARY_CARE_INDICATOR>N</PRIMARY_CARE_INDICATOR>
</PROVIDER>
<PROVIDER>
   <NPI>1234567890</NPI>
   <FIRST_NAME>Pam</FIRST_NAME>
   <LAST_NAME>Zoe</LAST_NAME>
   <MIDDLE_INITIAL />
   <GENDER>M</GENDER>
   <LINE_1_ADDRESS>14013 Albert way</LINE_1_ADDRESS>  
   <LINE_2_ADDRESS />
   <CITY>manassas</CITY>
   <STATE>VA</STATE>
   <ZIP>22010</ZIP>
   <COUNTY>042</COUNTY>
   <PHONE_NUMBER>7158470067</PHONE_NUMBER>
   <CLINIC_NAME>Prince william Hospital</CLINIC_NAME>
   <ACCEPT_NEW_PATIENT>Y</ACCEPT_NEW_PATIENT>
   <PRIMARY_CARE_INDICATOR>N</PRIMARY_CARE_INDICATOR>
</PROVIDER></root>');

commit;



CREATE OR REPLACE procedure load_xml
IS

BEGIN

DECLARE

	v_lob			 clob;

	BEGIN

	select col1 into v_lob from temp_clob;

	for i in ( select value(p) myNode from temp_clob i, table(xmlsequence(extract(xmltype(col1), '/root/*'))) p) loop

	insert into tab1 values(
		extractvalue(i.myNode,'/PROVIDER/FIRST_NAME'),
		extractvalue(i.myNode,'/PROVIDER/LAST_NAME')
	);

	end loop;

	END;

END;
/

show errors

exec load_xml;
select * from tab1;

Select allOpen in new window

Could you help  me on this xml . Instead of inserting I can upload this xml file into clob table. I would like to upload the xml data to the Oracle 10g .
I have an xml file as follows

<HMO DATE="2010-02-18">
<HMO_GENERAL_INFORMATION>
<MCO_ID_LIST>
   <MCO_ID>12345678</MCO_ID>  
   <MCO_ID>87654321</MCO_ID>  
   </MCO_ID_LIST>
   <HMO_WEBSITE_ADDRESS>www.mhadfgwi.com</HMO_WEBSITE_ADDRESS>
   <HMO_DAILY_CONTACT_EMAIL_ADDRESS>cscott@centene.com</HMO_DAILY_CONTACT_EMAIL_ADDRESS>
   <HMO_MONTHLY_CONTACT_EMAIL_ADDRESS>cscott@centene.com</HMO_MONTHLY_CONTACT_EMAIL_ADDRESS>
   </HMO_GENERAL_INFORMATION>
<PROVIDERS RECORDS="2">
<PROVIDER>
   <NPI>0987654321</NPI>
   <FIRST_NAME>Pam</FIRST_NAME>
   <LAST_NAME>Zie</LAST_NAME>
   <MIDDLE_INITIAL />
   <GENDER>F</GENDER>
   <LINE_1_ADDRESS>3333 PR Blvd</LINE_1_ADDRESS>  
   <LINE_2_ADDRESS />
   <CITY>Manassas</CITY>
   <STATE>VA</STATE>
   <ZIP>50100</ZIP>
   <COUNTY>037</COUNTY>
   <PHONE_NUMBER>0987654321</PHONE_NUMBER>
   <CLINIC_NAME>AW Hospital</CLINIC_NAME>
   <ACCEPT_NEW_PATIENT>Y</ACCEPT_NEW_PATIENT>
   <PRIMARY_CARE_INDICATOR>N</PRIMARY_CARE_INDICATOR>
   <FOREIGN_LANGUAGES>
   <CODE >345</CODE>
  <CODE>346</CODE>
   </FOREIGN_LANGUAGES>
   <SPECIALTIES>
   <CODE>301</CODE>
  <CODE>302</CODE>
   </SPECIALTIES>
</PROVIDER>
<PROVIDER>
   <NPI>1234567890</NPI>
   <FIRST_NAME>Pame</FIRST_NAME>
   <LAST_NAME>linski</LAST_NAME>
   <MIDDLE_INITIAL />
   <GENDER>F</GENDER>
   <LINE_1_ADDRESS>33 Ridge Blvd</LINE_1_ADDRESS>  
   <LINE_2_ADDRESS />
   <CITY>centreville</CITY>
   <STATE>VA</STATE>
   <ZIP>12345</ZIP>
   <COUNTY>038</COUNTY>
   <PHONE_NUMBER>890765234</PHONE_NUMBER>
   <CLINIC_NAME>Asp Hospital</CLINIC_NAME>
   <ACCEPT_NEW_PATIENT>Y</ACCEPT_NEW_PATIENT>
   <PRIMARY_CARE_INDICATOR>Y</PRIMARY_CARE_INDICATOR>
   <FOREIGN_LANGUAGES>
   <CODE>401</CODE>
  <CODE>402</CODE>
   </FOREIGN_LANGUAGES>
   <SPECIALTIES>
   <CODE>322</CODE>
  <CODE>422</CODE>
   </SPECIALTIES>
</PROVIDER>
</PROVIDERS>
</HMO>

The actual file may contain more than 10000 provider information.
What I would like to do is insert this provider information into oracle table.
For each MCO_ID provider information will be  repeated.
For each foreign languages and specialties the provider information will be repeat with same MCO_ID
For example after inserting the records there will be 16 records in the table as follows

Mco_id               NPI              first_name  last_name …….         foreign_languages  specialties
12345678        0987654321      Pam             Zie        ........           345                        301
12345678        0987654321      Pam             Zie        ........           345                        302
12345678        0987654321      Pam             Zie        ........           346                        301
12345678        0987654321      Pam             Zie        ........           346                        302
12345678        1234567890      Pame            linski     ........          401                      322
12345678        1234567890      Pame            linski     ........          401                      422
12345678        1234567890      Pame            linski      ........         402                      322
12345678        1234567890      Pame             linski     ........          402                      422

87654321            0987654321      Pam             Zie        ........           345                        301
87654321            0987654321      Pam             Zie        ........           345                        302
87654321            0987654321      Pam             Zie        ........           346                        301
87654321            0987654321      Pam             Zie        ........           346                        302
87654321            1234567890      Pame            linski     ........          401                      322
87654321            1234567890      Pame            linski     ........          401                      422
87654321            1234567890      Pame            linski      ........         402                      322
87654321            1234567890      Pame             linski     ........          402                      422

Please help me!

Here goes.  Everything is tested using 10.2.0.3.

run the script in the code window.  notice I added the MCO_ID and NPI column.  Hopefully adding the rest is straight forward.

Assuming the XML you posted is in a file called: c:\q.txt

Create a file called myControlFile.ctl that contains the following:
------------------------------------------
load data
infile *
replace
into table temp_clob
fields terminated by ','
(
 LOB_PATH  filler char,
 col1 LOBFILE(LOB_PATH) TERMINATED BY EOF
)
begindata
c:\q.txt

then from a command prompt run:
sqlldr username/password control=c:\myControlFile.ctl

then from a SQL prompt:
exec load_xml
select * from tab1;

drop table tab1 purge;
create table tab1(MCO_ID varchar2(10), NPI varchar2(10), first_name varchar2(100), last_name varchar2(100));

drop table temp_clob purge;
create table temp_clob(col1 clob);

CREATE OR REPLACE procedure load_xml
IS

BEGIN

DECLARE

	v_lob			 clob;

	BEGIN

	select col1 into v_lob from temp_clob;

	for q in ( select extractvalue(value(p),'/MCO_ID') myMCO_ID from temp_clob i, table(xmlsequence(extract(xmltype(col1), '/HMO/HMO_GENERAL_INFORMATION/MCO_ID_LIST/*'))) p) loop

		for i in ( select value(p) myNode from temp_clob i, table(xmlsequence(extract(xmltype(col1), '/HMO/PROVIDERS/*'))) p) loop
	
			insert into tab1(mco_id,npi,first_name,last_name) values(
				q.myMCO_ID,
				extractvalue(i.myNode,'/PROVIDER/NPI'),
				extractvalue(i.myNode,'/PROVIDER/FIRST_NAME'),
				extractvalue(i.myNode,'/PROVIDER/LAST_NAME')
			);

		end loop;
	end loop;

	END;

END;
/

show errors

Select allOpen in new window

I tried with your code, yes it is working. Again the problem with "Foreign_LANGUAGES and SPECIALTIES"
I don't know whether you notified it, it repeating inside each provider record.
I added one more for loop but those codes inserting for all providers instead of corresponding provider
Please help me.

sorry.  I missed the other repeating fields.  I'll see what I can do but I'm almost done for the day.

It will be some time tomorrow.

no prob

See if this gets you what you need.

Disclaimer:
This probably isn't the most efficient way but should be OK for 10,000 rows.  Let me know how it performs.  The other way is to use the The Document Object Model (DOM) and create multiple node lists and go through the same number of loops.

Using the DOM is pretty ugly which is why I didn't provide any examples.  If this code doesn't work well for you, I'll see if I can mock up a DOM example.

drop table tab1 purge;
create table tab1(MCO_ID varchar2(10), NPI varchar2(10), first_name varchar2(100), last_name varchar2(100), foreign_languages char(3), specialties char(3));

CREATE OR REPLACE procedure load_xml
IS

BEGIN

DECLARE

	v_xmltype			 xmltype;

	BEGIN

	select xmltype(col1) into v_xmltype from temp_clob;

	for q in ( select extractvalue(value(p),'/MCO_ID') myMCO_ID from table(xmlsequence(extract(v_xmltype, '/HMO/HMO_GENERAL_INFORMATION/MCO_ID_LIST/*'))) p) loop

		for i in ( select value(p) myNode from table(xmlsequence(extract(v_xmltype, '/HMO/PROVIDERS/*'))) p) loop

			for z in ( select extractvalue(value(p),'/CODE') mySPEC_CODE from table(xmlsequence(extract(i.myNode, '/PROVIDER/SPECIALTIES/*'))) p) loop

				for x in ( select extractvalue(value(p),'/CODE') myLANG_CODE from table(xmlsequence(extract(i.myNode, '/PROVIDER/FOREIGN_LANGUAGES/*'))) p) loop

					insert into tab1(mco_id,npi,first_name,last_name,foreign_languages, specialties) values(
						q.myMCO_ID,
						extractvalue(i.myNode,'/PROVIDER/NPI'),
						extractvalue(i.myNode,'/PROVIDER/FIRST_NAME'),
						extractvalue(i.myNode,'/PROVIDER/LAST_NAME'),
						x.myLANG_CODE,
						z.mySPEC_CODE
					);

				end loop;
			end loop;
		end loop;
	end loop;

	END;

END;
/

show errors

Select allOpen in new window

Is there any specific reason , it works only for 10,000 rows

I don't believe I said there is a limit.  The only limit should be the 4Gig size for a CLOB.

I was trying to point out that there might be more efficient ways to do this if the file exceeds some tipping point.  There will probably be some theoretical number where using the DOM will be faster than the approach I posted.

I used the 10,000 number as an example since it's what you gave me in http:#a32645758.  I was saying that the approach I used versus the DOM probably won't matter for 10,000 records.

If you test my code against a real-world example and the performance isn't acceptable, let me know and I'll see if I can mock up a DOM example.

Sure let me test it against real world  example. And let you know. I reaally appreciated if you can provide me DOM example.

If I get some free time later I'll see what I can do.

How to get this date into the field, Sorry I didn't mention that earlier
This is starting point of HMO and  this date is same for all records.

 <HMO DATE="2010-02-18">

This is working in small xml file. I tried to load the real-world  xml file. Unfortunately I could not make it at this point because of network issue.
I need one more help.  How to compare the xml file with xsd file Do I need to raise the new question for this.

It will probably be a different question but:

XSDs are an XML Schema file which allows you to 'validate' data contained in an XML file.

What do you mean compare xml with xsd?

How to validate? do we need to write some procedure for it.

At a high level:
Like most things in Oracle and XML, it depends on your specific requirements.

I assume you want to tuck the XSD file away somewhere and validate the incoming CLOB field at the top of the procedure I provided.  Sort of: If one part fails validation reject the entire batch?

This should probably be a separate question but until it's opened, here's a link that talks about something similar that I did a few years ago.  It's a little long and drags out but it has what you might need:

Note: It uses an Oracle directory to hold the XSD file.
https://www.experts-exchange.com/questions/22409097/XML-schema-registration-and-validation-in-Oracle.html

Can we upload the xml file with EXTERNAL TABLES

You should be able to use an external table to load the entire file at once as a CLOB but you can't parse it as individual fields because of the same reasons you can't use sql*Loader.

Could you please take a look on question ID: 26211931

could you please take a look on question ID :26249676