CREATE OR REPLACE FUNCTION username.parse_xml
( p_string     IN     VARCHAR2
, p_node       IN     VARCHAR2
)  RETURN VARCHAR2 AS
v_data                 VARCHAR2(4000);
v_node                 VARCHAR2(4000);
v_upper_str            VARCHAR2(4000);
v_startnode            VARCHAR2(4000);
v_endnode              VARCHAR2(4000);
v_startnodelength      NUMBER;
BEGIN
v_node               := UPPER( p_node );
v_upper_str          := UPPER( p_string );
v_startnode          := ‘<‘ || v_node || ‘>’;
v_endnode            := ‘</’ || v_node || ‘>’;
v_startnodelength    := LENGTH( v_startnode );
v_data := SUBSTR( p_string
, INSTR( v_upper_str, v_startnode ) + v_startnodelength
);
v_data := SUBSTR( v_data
, 1
, INSTR( UPPER( v_data ), v_endnode ) – 1
);
RETURN( TRIM(v_data) );
END;
/

CREATE TABLE username.XML_TABLE
(
XML  VARCHAR2(4000 BYTE)                          NULL
)
ORGANIZATION EXTERNAL
(  TYPE ORACLE_LOADER
DEFAULT DIRECTORY TMP
ACCESS PARAMETERS
( RECORDS DELIMITED BY ‘</item>’
BADFILE TMP’xml_file.bad’
LOGFILE TMP’xml_file.log’
FIELDS TERMINATED BY “,” LDRTRIM
)
LOCATION (TMP’xml_file.xml’)
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

SELECT ROWNUM item_id
, username.parse_xml ( xml, ‘col1’ ) col1
, username.parse_xml ( xml, ‘col2’ ) col2
, username.parse_xml ( xml, ‘col3’ ) col3
, username.parse_xml ( xml, ‘col4’ ) col4
, username.parse_xml ( xml, ‘col5’ ) col5
FROM   username.XML_TABLE;