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

Parsing XML Documents Using XPath In Oracle

Oracle has XMLTYPE data type to work on XML objects inside database. (For more information please visit otn ) You can parse XML documents using XPath queries(For more information please visit w3 ) I demonstrate a simple example to show how to parse XML documents witj PL/SQL


DECLARE
  orginal_xml xmltype;
  extracted_xml xmltype;
BEGIN
  orginal_xml := xmltype.createxml(
                  '
                   
                        Roberto Carlos
                   
                   
                        Arsene Lupin
                   
                   
                        Elvis Presley
                   
                  '
                  );
  dbms_output.put_line( 'Orginal XML :' );
  dbms_output.put_line(orginal_xml.getStringVal());
 
  dbms_output.put_line( lpad('-',100, '-' ) );
  extracted_xml := orginal_xml.extract('//students/student[ position() = 1 ]');
  dbms_output.put_line( 'XPath Expression : ' || '//students/student[ position() = 1 ] '  );
  dbms_output.put_line( extracted_xml.getStringVal() );
 
  dbms_output.put_line( lpad('-',100, '-' ) );
  extracted_xml := orginal_xml.extract('//students/student[ position() = last() ]/name/attribute::id');
  dbms_output.put_line( 'XPath Expression : ' || '//students/student[ position() = last() ]/name/attribute::id'  );
  dbms_output.put_line( extracted_xml.getStringVal() );
END;
The output is

Orginal XML :
   
        Roberto Carlos
   
   
        Arsene Lupin
   
   
        Elvis Presley
   
   

----------------------------------------------------------------------------------------------------
XPath Expression : //students/student[ position() = 1 ]

  Roberto Carlos

----------------------------------------------------------------------------------------------------
XPath Expression : //students/student[ position() = last() ]/name/attribute::id
1899

Processing XML Data On Oracle Database

Oracle, bir programlama dilinden beklenebilecek bütün özellikleri beraberinde getirmiştir 10G sürümü ile. Bunlardan biri de XML verileri işlemedir. XML yakın zamanlarda kullanılan bir standart haline gelmesi, dağıtık uygulamalar tarafından kullanılması, düz metinden oluşması popülaritesini arttırmaktadır. Oracle ile XML işlerken elde ettiğim en temel fonksiyonları, bu alanda bir başlangıç yapmak için inceleyebilirsiniz.


Öncelikle bir tablo oluşturalım.
create table students(id number, full_name varchar2(32));

İçine test için veri girelim.
BEGIN
INSERT INTO students VALUES (1889, 'Roberto Carlos');
INSERT INTO students VALUES (1890, 'Arsene Lupin');
COMMIT;
END;




Sorgular, içinde kullanılan fonksiyonların anlamları ve çıktıları belirtilmiştir.
--XMLAGG : Takes parameter an xmltype and aggregates
SELECT XMLELEMENT("STUDENTS", XMLAGG(XMLELEMENT("STUDENT", s.full_name)))
FROM students s
/*

    Roberto Carlos
    Arsene Lupin
*/


--XMLFOREST parametre aldığı değerleri xml e dönüştürür.
SELECT XMLELEMENT("STUDENTS", XMLAGG(XMLELEMENT("STUDENT", XMLFOREST(s.ID, s.full_name))))
FROM students s
/*

    
        1889
        Roberto Carlos
    
    
        1890
        Arsene Lupin
    
*/



SELECT XMLELEMENT("student", XMLATTRIBUTES(ID AS "id"), XMLELEMENT("name", full_name))
FROM students
/*

         Roberto Carlos

         Arsene Lupin
*/

--Sub Query + function
SELECT XMLELEMENT("student", xmlattributes(TO_CHAR((SELECT SYSDATE FROM DUAL), 'DD.MM.YYYY HH24:MI:SS') AS
"xml_create_date"), XMLELEMENT("name", xmlattributes(ID AS "id"), full_name))
FROM students
/*

         Roberto Carlos

         Arsene Lupin
*/

--
SELECT XMLFOREST(id, full_name) FROM students
/*
1889
Roberto Carlos
1890
Arsene Lupin
*/