rem load.bat loads country_state.xml into Oracle database via sqlldr via two parameters of username and password RJM Programming 2013
if %1TRICK==TRICK goto nomore
if %2TRICK==TRICK goto nomore

echo CREATE TABLE country_state_xml_load OF XMLType; > load.sql
echo exit >> load.sql
sqlplus %1/%2 @load.sql

echo LOAD DATA > load.ctl
echo INFILE * >> load.ctl
echo INTO TABLE COUNTRY_STATE_XML_LOAD TRUNCATE >> load.ctl
echo xmltype(xmldata) >> load.ctl
echo FIELDS >> load.ctl
echo ( >> load.ctl
echo the_fname filler char(100), >> load.ctl
echo xmldata LOBFILE (the_fname) TERMINATED BY EOF >> load.ctl
echo ) >> load.ctl
echo BEGINDATA >> load.ctl
echo country_state.xml >> load.ctl

sqlldr %1/%2 load.ctl

echo SELECT value(d) > select.sql
echo  FROM country_state_xml_load c, >> select.sql
echo  table(xmlsequence(extract(c.object_value,'/INFO/COUNTRY/STATE'))) d >> select.sql
echo  WHERE existsNode(c.object_value, '/INFO/COUNTRY/STATE') = 1; >> select.sql
 
echo SELECT value(d)  >> select.sql
echo  FROM country_state_xml_load c, >> select.sql
echo  table(xmlsequence(extract(c.object_value,'/INFO/COUNTRY/EXTERNAL'))) d >> select.sql
echo  WHERE existsNode(c.object_value, '/INFO/COUNTRY/EXTERNAL') = 1; >> select.sql
 
echo SELECT value(d)  >> select.sql
echo  FROM country_state_xml_load c, >> select.sql
echo  table(xmlsequence(extract(c.object_value,'/INFO/COUNTRY/TERRITORY'))) d >> select.sql
echo  WHERE existsNode(c.object_value, '/INFO/COUNTRY/TERRITORY') = 1; >> select.sql
sqlplus %1/%2 @select.sql

goto fin
:nomore
echo Need username and password to login to Oracle sqlplus and sqlldr
:fin