Oracle XML

What is XML and what is it used for?

XML (eXtensible Markup Language) is a W3C initiative that allows information and services to be encoded with meaningful structure and semantics that both computers and humans can understand. XML is great for information exchange, and can easily be extended to include user-specified and industry-specified tags. Look at this simple example defining a FAQ:

        <?xml version="1.0"?>
	<!DOCTYPE question-list SYSTEM "faq.dtd">
        <?xml-stylesheet type="text/xml" href="faq.xsl"?>

        <FAQ-LIST>
           <QUESTION>
                <QUERY>Question goes here</QUERY>
                <RESPONSE>Answer goes here.</RESPONSE>
           </QUESTION>
           <QUESTION>
                <QUERY>Another question goes here.</QUERY>
                <RESPONSE>The answer goes here.</RESPONSE>
           </QUESTION>
        </FAQ-LIST>

What is a DTD and what is it used for?

A Document Type Definition (DTD) defines the elements or record structure of a XML document. A DTD allows your XML files to carry a description of its format with it. The DTD for the above XML example looks like this:

	<?xml version="1.0"?>

	<!ELEMENT faq-list (question+)>
	   <!ELEMENT question (query, response*)>
	      <!ELEMENT query    (#PCDATA)>
	      <!ELEMENT response (#PCDATA)>

Notes:

#PCDATA (parsed character data) means that the element contains data that can be parsed by a parser like HTML
The + sign in the example above declares that the "QUESTION" element must occur one or more times inside the "FAQ-LIST" element.
The * sign in the example above declares that the "QUERY" element can occur zero or more times inside the "QUESTION" element.

The W3C also formulated a new standard, called XML Schemas that superceded DTD's. Schemas allow for more complex data types within your tags and better ways to constrain (validate) data within these tags.

What is XML Schema and what is it used for?
A DTD cannot easily be converted into a CREATE TABLE statement as DTD's only describe character data. The W3C defined a XML language called XML Schema that can better describe data (with propper data types). XML Schemas can easily be converted into CREATE TABLE statments. Look at this example:

   <?xml version="1.0" encoding="UTF-8"?>
   <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
    <xs:element name="FAQ-LIST">
        <xs:annotation>
            <xs:documentation>Example XML schema</xs:documentation>
        </xs:annotation>

        <xs:complexType>
            <xs:sequence maxOccurs="unbounded">
                <xs:element name="QUESTION">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="QUERY"/>
                            <xs:element name="RESPONSE"/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
   </xs:schema>

Note: It is quite difficult to compile XML Schemas by hand. Best would be to get a tool like XMLSpy to help you getting the job done.

What XML compliant products do Oracle provide?

XMLDB
XML-SQL Utility (XSU)
XML Developer's kits (XDK) for Java, C, C++ and PL/SQL.
See demo programs in the $ORACLE_HOME/xdk/ directory.
XSQL Servlet
Processing SQL queries and generating an XML result set
Other products
Oracle integrated XML in various other product offerings like the Oracle iFS, Oracle InterMedia, JDeveloper, etc.

How does one store and extract XML data from Oracle?

XML data can be stored in Oracle (9.2.0 and above) using the XMLType data type. Look at this example:

connect scott/tiger

create table XMLTable (doc_id number, xml_data XMLType);

insert into XMLTable values (1,
        XMLType('<FAQ-LIST>
           <QUESTION>
                <QUERY>Question 1</QUERY>
                <RESPONSE>Answer goes here.</RESPONSE>
           </QUESTION>
        </FAQ-LIST>'));

select extractValue(xml_data, '/FAQ-LIST/QUESTION/RESPONSE')  -- XPath expression
from   XMLTable
where  existsNode(xml_data, '/FAQ-LIST/QUESTION[QUERY="Question 1"]') = 1;

Can one index XML data loaded into the database?
Yes, look at this example:
create index XMLTable_ind on XMLTable
(extractValue(xml_data, '/FAQ-LIST/QUESTION/QUERY') );

More Information
http://www.oracle.com/technology/tech/xml/index.html
http://www.oracle.com/technology/obe/10gr2_db_vmware/datamgmt/xquery/xquery.htm

Some Examples:

- Select Data to XML
The simplest way to generate an XML document is the usage of the package XMLGEN. The function getXml() takes the parameters for the SQL query and the meta data type (such as DTD) and returns a CLOB containing the XML document.
Here we will select one row for the department table and 3 rows for the employee table in the same XML row.

set long 9999999 
set linesize 0
set pagesize 0
set header off
select dbms_xmlgen.getxml( 'select deptno, dname, cursor( select empno, ename from emp 
                                                                  where emp.deptno = dept.deptno ) employee 
                              from dept 
                              where deptno = 10' ) 
  from dual;

<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <EMPLOYEE>
   <EMPLOYEE_ROW>
    <EMPNO>7782</EMPNO>
    <ENAME>CLARK</ENAME>
   </EMPLOYEE_ROW>
   <EMPLOYEE_ROW>
    <EMPNO>7839</EMPNO>
    <ENAME>KING</ENAME>
   </EMPLOYEE_ROW>
   <EMPLOYEE_ROW>
    <EMPNO>7934</EMPNO>
    <ENAME>MILLER</ENAME>
   </EMPLOYEE_ROW>
  </EMPLOYEE>
 </ROW>
</ROWSET>

- Select Data to XML Defining my OWN headers
set long 32000
SELECT XMLELEMENT("EMP_TABLE",
        (select XMLELEMENT("EMP_ROW", XMLFOREST(empno, ename, job, mgr, hiredate, sal, deptno)
                          )
           from   emp
           where empno = 7369))
from dual;

<EMP_TABLE>
<EMP_ROW>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>17/dec/80 00:00:00</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
</EMP_ROW>
</EMP_TABLE>

- Generate an XML File Selecting Data from Tables:
-- CREATE OR REPLACE PROCEDURE rajnxmltest
-- IS

SET SERVEROUTPUT ON
DECLARE
v_file Utl_File.File_Type;
v_xml   CLOB;
v_more BOOLEAN := TRUE;
BEGIN
-- Create XML document from query.
v_xml := DBMS_XMLQuery.GetXML('Select E.EMPNO, E.ENAME, E.JOB, E.HIREDATE, E.SAL, E.DEPTNO, D.DNAME, d.loc
                                   From EMP E, dept d
                                   where D.DEPTNO = E.DEPTNO
                                   order by 1');

-- Output XML document to file.
v_file := Utl_File.FOpen('/export/home/oracle/exportfiles', 'test1.xml', 'w');
WHILE v_more LOOP
    Utl_File.Put(v_file, Substr(v_xml, 1, 32767));
    IF Length(v_xml) > 32767 THEN
      v_xml := Substr(v_xml, 32768);
    ELSE
      v_more := FALSE;
    END IF;
END LOOP;
Utl_File.FClose(v_file);
/*EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
    Utl_File.FClose(v_file);*/
END;
/

A file like this will be generated:
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
      <DNAME>RESEARCH</DNAME>
      <LOC>DALLAS</LOC>
   </ROW>
   <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <DEPTNO>30</DEPTNO>
      <DNAME>SALES</DNAME>
      <LOC>CHICAGO</LOC>
   </ROW>
   <ROW num="3">
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <DEPTNO>30</DEPTNO>
      <DNAME>SALES</DNAME>
      <LOC>CHICAGO</LOC>
   </ROW>
</ROWSET>

More Examples
-- First let us establish a working set of tables and data.
create table dept (dno number,dname varchar2(30));
create table emp (eno number,dno number,ename varchar2(30));
create table timesheet (medate date,eno number, hours number);
create table project (pno number,pname varchar2(30),dno number);
create table workassignment (wno number,eno number, pno number);

insert into dept values (1,'Dept1');
insert into emp values (11,1,'Diego');
insert into timesheet values (trunc(last_day(sysdate)),11,160);
insert into project values (111,'Proj1',1);
insert into workassignment values (1111,11,111);
commit;

XMLELEMENT
XMLAGG
is an aggregate function. It takes a collection of XML fragments and returns an aggregated XML document. Any arguments that return null are dropped from the result.
XMLAGG is aggregate function that produces a forest of XML elements from the given list of xml elements. It concatenates the values returned from one column of multiple rows, unlike XMLCONCAT, which concatenates the values returned from multiple columns in the same row.
The order of element in the result of XMLAGG is defined by the order of retrieval of the source data rows. It is important to remember that the order of rows in an SQL resultset defined only if there is an explicit ORDER BY clause. Hence if the order of elements in the resulting forest is important then XMLAGG should be applied to data that comes from inner SELECT statement that has an ORDER BY clause, not e.g. from a table reference.

XMLFOREST

set long 9999999

DECLARE
   -- Data Variables
   v_xml              XMLTYPE;
   v_blob             BLOB;
   v_data_length      NUMBER;

   -- Loop Control Variables
   v_offset           NUMBER             DEFAULT 1;
   v_chunk   CONSTANT NUMBER             DEFAULT 4000;

   -- UTL_FILE variables
   fh       UTL_FILE.file_type;

BEGIN
   -- A big query, about 27k on my PC
   SELECT XMLELEMENT ("Employees", XMLAGG (emp_dept))
     INTO v_xml
     FROM (SELECT XMLELEMENT
                     ("Employee",
                      XMLFOREST
                         (e.EMPNO AS "EmployeeId",
                          e.ENAME AS "Name",
                          e.JOB AS "Job",
                          e.MGR AS "Manager",
                          e.HIREDATE AS "HireDate",
                          e.SAL AS "Salary",
                          e.COMM AS "Commission",
                          XMLFOREST (d.DEPTNO AS "DeptNo",
                                     d.DNAME AS "DeptName",
                                     d.LOC AS "Location"
                                    ) AS "Dept"
                         )
                     ) emp_dept
             FROM emp e, dept d
            WHERE e.DEPTNO = d.DEPTNO);

   -- Turn the XML into a BLOB to bypass any 4k and 32k issues
   v_blob := v_xml.getblobval (1);
   v_data_length := DBMS_LOB.getlength (v_blob);

   -- Open the file
   fh := UTL_FILE.fopen ('XML_FILES', 'myxml.xml', 'wb', v_chunk);

   -- Da loop de loop
   LOOP
      -- Exit when our file offset is bigger than our file
      EXIT WHEN v_offset > v_data_length;

      -- Write the output chunk by chunk
      UTL_FILE.put_raw (fh, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset),
                        TRUE);

      -- Increment the offset by the amount written
      v_offset := v_offset + v_chunk;
   END LOOP;

   -- Close the file and say byebye
   UTL_FILE.fclose (fh);

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      -- We won't write any data, or even open the file,
      -- if the query return no rows
      NULL;
END;
/

<?xml version="1.0" encoding="US-ASCII"?>
<Employees>
<Employee>
    <EmployeeId>7369</EmployeeId>
    <Name>SMITH</Name>
    <Job>CLERK</Job>
    <Manager>7902</Manager>
    <HireDate>1980-12-17</HireDate>
    <Salary>800</Salary>
    <Dept>
      <DeptNo>20</DeptNo>
      <DeptName>RESEARCH</DeptName>
      <Location>DALLAS</Location>
    </Dept>
</Employee>
<Employee>
    <EmployeeId>7839</EmployeeId>
    <Name>KING</Name>
    <Job>PRESIDENT</Job>
    <HireDate>1981-11-17</HireDate>
    <Salary>5000</Salary>
    <Dept>
      <DeptNo>10</DeptNo>
      <DeptName>ACCOUNTING</DeptName>
      <Location>NEW YORK</Location>
    </Dept>
</Employee>
<Employee>
    <EmployeeId>7934</EmployeeId>
    <Name>MILLER</Name>
    <Job>CLERK</Job>
    <Manager>7782</Manager>
    <HireDate>1982-01-23</HireDate>
    <Salary>1300</Salary>
    <Dept>
      <DeptNo>10</DeptNo>
      <DeptName>ACCOUNTING</DeptName>
      <Location>NEW YORK</Location>
    </Dept>
</Employee>
</Employees>

--more rows
set long 9999999
   SELECT XMLELEMENT ("Departments", XMLAGG (emp_dept))
     FROM (SELECT XMLELEMENT ("Employee",
                      XMLFOREST
                         (e.eno AS "EmployeeId",
                          e.dno AS "Department_Number",
                          e.ename AS "Employee_Name",
                          XMLFOREST (d.dno AS "DeptNo",
                                     d.dname AS "DeptName"
                                    ) AS "Dept"
                         )
                     ) emp_dept
             FROM emp e, dept d
            WHERE e.dno = d.dno);

--less rows
SELECT XMLELEMENT ("Departments", XMLAGG (emp_dept))
     FROM (SELECT XMLELEMENT ("Employee",
                      XMLFOREST
                         (e.eno AS "EmployeeId",
                          e.dno AS "Department_Number",
                          e.ename AS "Employee_Name"
                         )
                     ) emp_dept
             FROM emp e, dept d
            WHERE e.dno = d.dno);

--BARRY working but 1 less level
set long 9999999
   SELECT XMLELEMENT ("SMICR", XMLAGG (data))
     FROM (SELECT XMLELEMENT ("PpayFile",
                      XMLFOREST
                         (A.PPAYSEQ,
            A.PPAYOPER,
            A.PPAYOLDACT,
            A.PPAYOLDSER,
            A.PPAYOLDAMT,
            A.PPAYNEWACT,
            A.PPAYNEWSER,
            A.PPAYNEWAMT,
            A.PPAYSTATE
            )
                     ) data
      FROM ABN_TEMP_COLLECT_PPAYITEM A);



--BARRY working WITH 1 more level
set long 9999999
set heading on
set feedback off
set pagesize 0
set trimspool on
SELECT XMLELEMENT ("SMICR", XMLAGG (data))
     FROM (SELECT XMLELEMENT ("PpayFile",
                      XMLFOREST
                         (XMLFOREST (A.PPAYSEQ,
                         A.PPAYOPER,
                         A.PPAYOLDACT,
                         A.PPAYOLDSER,
                         A.PPAYOLDAMT,
                         A.PPAYNEWACT,
                         A.PPAYNEWSER,
                         A.PPAYNEWAMT,
                         A.PPAYSTATE
                                    ) AS "PPayItem"
                         )
                     ) data
             FROM ABN_TEMP_COLLECT_PPAYITEM A)

spool anyfile.xml
/
spool off;

--BARRY working BUT on 10g ONLY
DECLARE
   -- Data Variables
   v_xml              XMLTYPE;
   v_blob             BLOB;
   v_data_length      NUMBER;

   -- Loop Control Variables
   v_offset           NUMBER             DEFAULT 1;
   v_chunk   CONSTANT NUMBER             DEFAULT 4000;

   -- UTL_FILE variables
   fh       UTL_FILE.file_type;

BEGIN
   -- A big query, about 27k on my PC
   SELECT XMLELEMENT ("SMICR", XMLAGG (data))
     INTO v_xml
     FROM (SELECT XMLELEMENT ("PpayFile",
                      XMLFOREST
                         (XMLFOREST (A.PPAYSEQ,
                         A.PPAYOPER,
                         A.PPAYOLDACT,
                         A.PPAYOLDSER,
                         A.PPAYOLDAMT,
                         A.PPAYNEWACT,
                         A.PPAYNEWSER,
                         A.PPAYNEWAMT,
                         A.PPAYSTATE
                                    ) AS "PPayItem"
                         )
                     ) data
             FROM ABN_TEMP_COLLECT_PPAYITEM A);

   -- Turn the XML into a BLOB to bypass any 4k and 32k issues
   v_blob := v_xml.getblobval (1);
   v_data_length := DBMS_LOB.getlength (v_blob);

   -- Open the file
   fh := UTL_FILE.fopen ('XML_FILES', '10gmyxml.xml', 'wb', v_chunk);

   -- Da loop de loop
   LOOP
      -- Exit when our file offset is bigger than our file
      EXIT WHEN v_offset > v_data_length;

      -- Write the output chunk by chunk
      UTL_FILE.put_raw (fh, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset),
                        TRUE);

      -- Increment the offset by the amount written
      v_offset := v_offset + v_chunk;
   END LOOP;

   -- Close the file and say byebye
   UTL_FILE.fclose (fh);

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      -- We won't write any data, or even open the file,
      -- if the query return no rows
      NULL;
END;
/

--BARRY trying to work on 9i, working on SOLARIS, but not FILESERVER
SET SERVEROUTPUT ON
DECLARE
v_ctx   DBMS_XMLQuery.ctxType;
v_file Utl_File.File_Type;
v_xml   CLOB;
v_more BOOLEAN := TRUE;
BEGIN
DBMS_OUTPUT.PUT_LINE('CCreate XML context');
-- Create XML context.
v_ctx := DBMS_XMLQuery.newContext('SELECT table_name, tablespace_name FROM user_tables WHERE rownum < 6');
DBMS_OUTPUT.PUT_LINE('after Create XML context');

-- Set parameters to alter default Rowset and Row tag names and default case.
DBMS_XMLQuery.setRowsetTag(v_ctx, 'user_tables');
DBMS_XMLQuery.setRowTag(v_ctx, 'table');
DBMS_XMLQuery.setTagCase(v_ctx, DBMS_XMLQuery.LOWER_CASE);

DBMS_OUTPUT.PUT_LINE('before add');
-- Add an IE specfic XSL stylesheet reference so browser can transform the file.
-- DBMS_XMLQuery.setStylesheetHeader(v_ctx, '/export/home/oracle/exportfiles/IEStyle.xsl', 'text/xsl');

DBMS_OUTPUT.PUT_LINE('Create the XML document');
-- Create the XML document.
v_xml := DBMS_XMLQuery.GetXML(v_ctx);
DBMS_XMLQuery.closeContext(v_ctx);

DBMS_OUTPUT.PUT_LINE('Output the XML document');
-- Output XML document to file.
v_file := Utl_File.FOpen('EXPORTPATH', 'testfromSOL.xml', 'w');
WHILE v_more LOOP
    Utl_File.Put(v_file, Substr(v_xml, 1, 32767));
    IF Length(v_xml) > 32767 THEN
      v_xml := Substr(v_xml, 32768);
    ELSE
      v_more := FALSE;
    END IF;
END LOOP;
Utl_File.FClose(v_file);

EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
    Utl_File.FClose(v_file);
END;
/

--BARRY trying to work on 9i, working on SOLARIS, but not FILESERVER
SET SERVEROUTPUT ON
DECLARE
v_ctx   DBMS_XMLQuery.ctxType;
v_file Utl_File.File_Type;
v_xml   CLOB;
v_more BOOLEAN := TRUE;
BEGIN
DBMS_OUTPUT.PUT_LINE('CCreate XML context');
-- Create XML context.
v_ctx := DBMS_XMLQuery.newContext('SELECT A.Ppayseq, A.Ppayoper, A.PPAYOLDACT, A.PPAYOLDSER, A.PPAYOLDAMT, A.PPAYNEWACT,
                                     A.PPAYNEWSER, A.PPAYNEWAMT, A.PPAYSTATE from ABN_TEMP_COLLECT_PPAYITEM A');
DBMS_OUTPUT.PUT_LINE('after Create XML context');

-- Set parameters to alter default Rowset and Row tag names and default case.
DBMS_XMLQuery.setRowsetTag(v_ctx, 'SMICR');
DBMS_XMLQuery.setRowTag(v_ctx, 'PPayFile');
DBMS_XMLQuery.setRowTag(v_ctx, 'PPayItem');
-- DBMS_XMLQuery.setTagCase(v_ctx, DBMS_XMLQuery.LOWER_CASE);

DBMS_OUTPUT.PUT_LINE('before add');
-- Add an IE specfic XSL stylesheet reference so browser can transform the file.
-- DBMS_XMLQuery.setStylesheetHeader(v_ctx, '/export/home/oracle/exportfiles/IEStyle.xsl', 'text/xsl');

DBMS_OUTPUT.PUT_LINE('Create the XML document');
-- Create the XML document.
v_xml := DBMS_XMLQuery.GetXML(v_ctx);
DBMS_XMLQuery.closeContext(v_ctx);

DBMS_OUTPUT.PUT_LINE('Output the XML document');
-- Output XML document to file.
v_file := Utl_File.FOpen('EXPORTPATH', 'testaaaaa1.xml', 'w');
WHILE v_more LOOP
    Utl_File.Put(v_file, Substr(v_xml, 1, 32767));
    IF Length(v_xml) > 32767 THEN
      v_xml := Substr(v_xml, 32768);
    ELSE
      v_more := FALSE;
    END IF;
END LOOP;
Utl_File.FClose(v_file);

EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
    Utl_File.FClose(v_file);
END;
/

More Resources:
Easy XML - Let the Database do the Work
http://dotnet.org.za/thea/archive/2005/02/15/14485.aspx
http://blogs.ittoolbox.com/oracle/guide/archives/get-the-xml-out-of-your-database-8716
http://forums.oracle.com/forums/thread.jspa?threadID=426163&tstart=0&messageID=1474128
http://asktom.oracle.com/~sdillon/rss.html
http://www.oracle.com/technology/oramag/oracle/05-sep/o55xml.html
http://www.oracle-base.com/articles/9i/SQLXML9i.php