Accessing XML data from SQL

Oracle has powerful utilities for developeers. The one that i like is utility function for playing XML data. Utility functions allows you to access XML file as if the XML data is inside a database table. Suppose that you have a company XML file and you have to access the XML data without uploading/importing/loading XML file into database. XMLTABLE is just stands for this reason. It has a special syntax-XQuery- for accessing XML data and its attributes. To understand better, please follow the demonstration below. XML file(company.xml) is resides server side file system(/home/oracle/Documents/). XMLTYPE, BFILE and XMLTABLE will be used:


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as mennan

SQL> 
SQL> 
SQL> 
SQL> 
SQL> CREATE OR REPLACE DIRECTORY COMPANY_REPORT_DIR AS '/home/oracle/Documents/';

Directory created
SQL> CREATE TABLE COMPANY_LIST
  2  (
  3    ID                VARCHAR2(4),
  4    COMPANY_TITLE     VARCHAR2(32)
  5  );

Table created
SQL> SELECT XMLTYPE(BFILENAME('COMPANY_REPORT_DIR', 'company.xml'), nls_charset_id('UTF8')).GetStringVal() AS XML_DATA FROM DUAL;

XML_DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-9"?>
<CompanyList>
  <Company>
    <Identifier>0001</Identifier>
    <Title>Oracle</Title>
  </Company>
  <Company>
    <Identifier>0002</Identifier>
    <Title>Microsoft</Title>
  </Company>
  <Company>
    <Identifier>0003</Identifier>
    <Title>Apple</Title>
  </Company>
  <Company>
    <Identifier>0004</Identifier>
    <Title>Google</Title>
  </Company>
</CompanyList>

SQL> INSERT INTO COMPANY_LIST ( ID, COMPANY_TITLE )
  2    SELECT x.ID, x.COMPANY_TITLE
  3      FROM (SELECT XMLTYPE(BFILENAME('COMPANY_REPORT_DIR', 'company.xml'), nls_charset_id('UTF8')) AS XML_DATA FROM DUAL) e,
  4           XMLTABLE('for $i in /CompanyList
  5                     return $i/Company'
  6                     PASSING XML_DATA
  7                     COLUMNS ID            VARCHAR2(4)   PATH 'Identifier',
  8                             COMPANY_TITLE VARCHAR2(32)  PATH 'Title'
  9                     ) x;

4 rows inserted
SQL> COMMIT;

Commit complete
SQL> SELECT * FROM COMPANY_LIST;

ID   COMPANY_TITLE
---- --------------------------------
0001 Oracle
0002 Microsoft
0003 Apple
0004 Google
SQL> DROP DIRECTORY COMPANY_REPORT_DIR;

Directory dropped
SQL> DROP TABLE COMPANY_LIST;

Table dropped

SQL> 




For more information please read the documentation : http://download.oracle.com/docs/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#ADXDB5121