CREATE TABLE country_state_xml OF XMLType;

CREATE TABLE country_state_xml_column (filename VARCHAR2(100), xml_document XMLType);

CREATE DIRECTORY temp AS 'c:\temp';
GRANT all on DIRECTORY temp to xdb;

INSERT INTO country_state_xml VALUES (XMLType(bfilename('TEMP', 'country_state.xml'), 
             nls_charset_id('AL32UTF8')));

INSERT INTO country_state_xml_column (filename, xml_document) VALUES (
             'country_state.xml',
             XMLType(bfilename('TEMP', 'country_state.xml'), 
             nls_charset_id('AL32UTF8')));


SELECT value(d) 
 FROM country_state_xml c,
 table(xmlsequence(extract(c.object_value,'/INFO/COUNTRY/STATE'))) d
 WHERE existsNode(c.object_value, '/INFO/COUNTRY/STATE') = 1;
 
SELECT value(d) 
 FROM country_state_xml c,
 table(xmlsequence(extract(c.object_value,'/INFO/COUNTRY/EXTERNAL'))) d
 WHERE existsNode(c.object_value, '/INFO/COUNTRY/EXTERNAL') = 1;
 
SELECT value(d) 
 FROM country_state_xml c,
 table(xmlsequence(extract(c.object_value,'/INFO/COUNTRY/TERRITORY'))) d
 WHERE existsNode(c.object_value, '/INFO/COUNTRY/TERRITORY') = 1;