Since Oracle 9i Release 1 (9.0.1), a new datatype, XMLType, has been introduced to facilitate native handling of XML data in the database.
The following example demonstrate how to read an XML file using the XMLType.
XML
<?xml version="1.0"?>
<customer_list>
<sales_person_staff_no>201201234</sales_person_staff_no>
<sales_person_name>Ada Cheng</sales_person_name>
<customer>
<customer_code>1234</customer_code>
<customer_name>ABC Trading Co., Ltd.</customer_name>
<contact>
<contact_name>Mickey Mouse</contact_name>
<phone_no>987654321</phone_no>
</contact>
<contact>
<contact_name>Donald Duck</contact_name>
<phone_no>987654322</phone_no>
</contact>
</customer>
<customer>
<customer_code>1235</customer_code>
<customer_name>Sunshine Co., Ltd.</customer_name>
<contact>
<contact_name>Felix the Cat</contact_name>
<phone_no>987654322</phone_no>
</contact>
</customer>
</customer_list>
PL/SQL
create or replace
PROCEDURE SP_XML_PARSING
(
as_if_dir IN VARCHAR2, /* Oracle Directory of the XML File */
as_if_name IN VARCHAR2 /* File Name of the XML File */
)
AS
/************************************************************************
NAME: SP_XML_PARSING
PURPOSE: Parse an XML file.
************************************************************************/
CURSOR c_customer_list (f_xml XMLTYPE) IS
SELECT customer_list.*
FROM XMLTable('/customer_list'
passing f_xml
COLUMNS STAFF_NO VARCHAR2(20) path 'sales_person_staff_no',
STAFF_NAME VARCHAR2(50) path 'sales_person_name',
CUSTOMERS XMLTYPE path 'customer'
) AS customer_list;
CURSOR c_customer (f_xml XMLTYPE) IS
SELECT customer.*
FROM XMLTable('/customer'
passing f_xml
COLUMNS CUSTOMER_CD VARCHAR2(20) path 'customer_code',
CUSTOMER_NAME VARCHAR2(100) path 'customer_name',
CONTACTS XMLTYPE path 'contact'
) AS customer;
CURSOR c_contacts (f_xml XMLTYPE) IS
SELECT contacts.*
FROM XMLTable('/contact'
passing f_xml
COLUMNS NAME VARCHAR2(50) path 'contact_name',
PHONE_NO VARCHAR2(50) path 'phone_no'
) AS contacts;
TYPE lt_customer_list IS TABLE OF c_customer_list%ROWTYPE INDEX BY BINARY_INTEGER;
TYPE lt_customer IS TABLE OF c_customer%ROWTYPE INDEX BY BINARY_INTEGER;
TYPE lt_contacts IS TABLE OF c_contacts%ROWTYPE INDEX BY BINARY_INTEGER;
la_customer_list lt_customer_list;
la_customer lt_customer;
la_contacts lt_contacts;
lx_if XMLTYPE;
BEGIN
lx_if := XMLTYPE(bfilename(as_if_dir, as_if_name), nls_charset_id('AL32UTF8'));
-- Customer List --
OPEN c_customer_list(lx_if);
LOOP
FETCH c_customer_list BULK COLLECT INTO la_customer_list;
FOR i IN 1..la_customer_list.COUNT LOOP
dbms_output.put_line('Staff No.: ' || la_customer_list(i).STAFF_NO);
dbms_output.put_line('Staff Name: ' || la_customer_list(i).STAFF_NAME);
dbms_output.put_line('**********************');
-- Customers --
IF c_customer%ISOPEN THEN
CLOSE c_customer;
END IF;
OPEN c_customer(la_customer_list(i).CUSTOMERS);
LOOP
FETCH c_customer BULK COLLECT INTO la_customer;
FOR j IN 1..la_customer.COUNT LOOP
dbms_output.put_line('Cusomter Code: ' || la_customer(j).CUSTOMER_CD);
dbms_output.put_line('Cusomter Name: ' || la_customer(j).CUSTOMER_NAME);
dbms_output.put_line('----------------------');
-- Contacts --
IF c_contacts%ISOPEN THEN
CLOSE c_contacts;
END IF;
OPEN c_contacts(la_customer(j).CONTACTS);
LOOP
FETCH c_contacts BULK COLLECT INTO la_contacts;
FOR k IN 1..la_contacts.COUNT LOOP
dbms_output.put_line('Contact Name: ' || la_contacts(k).NAME);
dbms_output.put_line('Phone No.: ' || la_contacts(k).PHONE_NO);
dbms_output.put_line('######################');
END LOOP;
EXIT WHEN c_contacts%NOTFOUND;
END LOOP;
-- End Contacts --
END LOOP;
EXIT WHEN c_customer%NOTFOUND;
END LOOP;
-- End Customers --
END LOOP;
EXIT WHEN c_customer_list%NOTFOUND;
END LOOP;
-- End Customer List --
CLOSE c_contacts;
CLOSE c_customer;
CLOSE c_customer_list;
END;
Output
Staff No.: 201201234 Staff Name: Ada Cheng ********************** Cusomter Code: 1234 Cusomter Name: ABC Trading Co., Ltd. ---------------------- Contact Name: Mickey Mouse Phone No.: 987654321 ###################### Contact Name: Donald Duck Phone No.: 987654322 ###################### Cusomter Code: 1235 Cusomter Name: Sunshine Co., Ltd. ---------------------- Contact Name: Felix the Cat Phone No.: 987654322 ######################
References
