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: 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 XML compliant products do Oracle provide?


How does one map relational data from tables to XML?

If you're using Oracle 8i, use the DBMS_XMLQUERY and DBMS_XMLSAVE JAVA based packages. For Oracle 9i, use the C-based package DBMS_XMLGEN.

Look at the following Oracle 9i code example:

	connect scott/tiger
set serveroutput on
DECLARE
Ctx DBMS_XMLGEN.ctxHandle; -- Var's to convert SQL output to XML
xml clob;
emp_no NUMBER := 7369;

xmlc varchar2(4000); -- Var's required to convert lob to varchar
off integer := 1;
len integer := 4000;
BEGIN
Ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp WHERE empno = '||emp_no);
DBMS_XMLGen.setRowsetTag(Ctx, 'EMP_TABLE');
DBMS_XMLGen.setRowTag(Ctx, 'EMP_ROW');
DBMS_XMLGEN.closeContext(Ctx);
xml := DBMS_XMLGEN.getXML(Ctx);

DBMS_LOB.READ(xml, len, off, xmlc); -- Display first part on screen
DBMS_OUTPUT.PUT_LINE(xmlc);
END;
/
The same results can be achieved using SQLX (see http://sqlx.org/). Some of the SQLX functions are XMLElement(), XMLForest(), XMLSequence(), etc. Look at this example.
	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;
An older Oracle 8i example:
	connect scott/tiger
set serveroutput on
DECLARE
Ctx DBMS_XMLQuery.ctxType; -- Var's to convert SQL output to XML
xml clob;
emp_no NUMBER := 7369;

xmlc varchar2(4000); -- Var's required to convert lob to varchar
off integer := 1;
len integer := 4000;
BEGIN
Ctx := DBMS_XMLQuery.newContext('SELECT * FROM emp WHERE empno = :empno');
DBMS_XMLQuery.setBindValue(Ctx, 'empno', emp_no);
xml := DBMS_XMLQuery.getXML(Ctx);
DBMS_XMLQuery.closeContext(Ctx);

DBMS_LOB.READ(xml, len, off, xmlc); -- Display first part on screen
DBMS_OUTPUT.PUT_LINE(xmlc);
END;
/

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;

The XML Developer Kit

Beginning with the first release of Oracle 8i, Oracle has offered the XDK for the Oracle database. For Oracle 8i releases 1 and 2 (8.1.5 and 8.1.6) you must download the XDK from Oracle Technology Network and install it manually. With Oracle 8i release 3 (8.1.7) and Oracle 9i, the XDK is integrated into the database and is installed automatically when the database is installed.

The XDK may be accessed from PL/SQL, Java, C, and C++ applications. PL/SQL applications must run inside the database (i.e. stored procedures, packages, triggers, or anonymous PL/SQL blocks) in order to access the XDK. Java applications may reside inside or outside the database. C and C++ applications, of course, reside outside the database.

In order to access the XDK from PL/SQL or Java applications residing inside the database, you must install Oracle’s JVM (sometimes called the Java option or Oracle JServer). It may seem counter-intuitive that you must have Oracle’s JVM installed in order to use the XDK from PL/SQL, but there is a reason for it: The XDK is itself written in Java. Oracle merely put PL/SQL wrappers on the Java code in order to make it accessible from PL/SQL.

The XDK contains an XML parser, an XSLT processor, an XPath engine, an XSQL page processor and servlet, and an XML SQL utility. The first three are Oracle’s implementations of the XML 1.0 specification, while the last two are features unique to Oracle.

Note that the XSQL page facility is only accessible from Java applications. Also note that in earlier releases of the XDK, the XML SQL utility was a separate tool that you had to install separately from the XDK. You can download the XML SQL utility from Oracle Technology Network.


Hello! Could you elaborate the example of serializing the XML into a table, 
let's say the XML file has data which corresponds to 2 tables (repetitive tags
for a detail table), is it possible?

Followup:
Sean here...

This is pretty easy to accomplish. There is no automated XML utility in Oracle
to insert a single XML document into two different tables... What you could do,
however, is create a join view on the two tables, then write an INSTEAD OF
trigger on the join view. Insert the XML document into the join view. The
INSTEAD OF trigger's job would be to insert rows into the appropriate tables
based on the values of the parent key found in each ROWSET of the XML document.

As an example, I have an XML document that looks like SO:

<?xml version = "1.0"?>
<ROWSET>
<ROW num="1">
<DEPTNO>10</DEPTNO>
<DNAME>SALES</DNAME>
<EMPNO>100</EMPNO>
<ENAME>MARK JOHNSON</ENAME>
</ROW>
<ROW num="2">
<DEPTNO>20</DEPTNO>
<DNAME>TECHNOLOGY</DNAME>
<EMPNO>200</EMPNO>
<ENAME>TOM KYTE</ENAME>
</ROW>
<ROW num="3">
<DEPTNO>20</DEPTNO>
<DNAME>TECHNOLOGY</DNAME>
<EMPNO>300</EMPNO>
<ENAME>SEAN DILLON</ENAME>
</ROW>
</ROWSET>

So you can see... the department data and the employee data co-mingled. We want
to normalize this into two tables... so here's what I'd do:

-----------------------------
system@SLAP> create table dept (
2 deptno number
3 primary key,
4 dname varchar2(30));
Table created.

system@SLAP> create table emp (
2 empno number
3 primary key,
4 deptno number,
5 ename varchar2(30));
Table created.

system@SLAP> create view deptemp as
2 select d.deptno, d.dname, e.empno, e.ename
3 from dept d, emp e
4 where d.deptno = e.empno;
View created.

system@SLAP> create or replace trigger deptemp_ioifer
2 instead of insert on deptemp
3 declare
4 begin
5 begin
6 insert into dept (deptno, dname)
7 values (:new.deptno, :new.dname);
8 exception
9 when DUP_VAL_ON_INDEX then
10 update dept
11 set dname = :new.dname
12 where deptno = :new.deptno;
13 end;
14 --
15 insert into emp (empno, deptno, ename)
16 values (:new.empno, :new.deptno, :new.ename);
17 end;
18 /
Trigger created.

system@SLAP> declare
2 l_clob clob := '<?xml version = "1.0"?>
3 <ROWSET>
4 <ROW num="1">
5 <DEPTNO>10</DEPTNO>
6 <DNAME>SALES</DNAME>
7 <EMPNO>100</EMPNO>
8 <ENAME>MARK JOHNSON</ENAME>
9 </ROW>
10 <ROW num="2">
11 <DEPTNO>20</DEPTNO>
12 <DNAME>TECHNOLOGY</DNAME>
13 <EMPNO>200</EMPNO>
14 <ENAME>TOM KYTE</ENAME>
15 </ROW>
16 <ROW num="3">
17 <DEPTNO>20</DEPTNO>
18 <DNAME>TECHNOLOGY</DNAME>
19 <EMPNO>300</EMPNO>
20 <ENAME>SEAN DILLON</ENAME>
21 </ROW>
22 </ROWSET>';
23
24 l_ctx dbms_xmlsave.ctxType;
25 l_rows number;
26 begin
27 l_ctx := dbms_xmlsave.newContext('DEPTEMP');
28 l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
29 dbms_xmlsave.closeContext(l_ctx);
30 dbms_output.put_line(l_rows || ' rows inserted...');
31 end insert_xml_emps;
32 /
PL/SQL procedure successfully completed.

system@SLAP> select * from dept;

DEPTNO DNAME
---------- ------------------------------
10 SALES
20 TECHNOLOGY

system@SLAP> select * from emp;

EMPNO DEPTNO ENAME
---------- ---------- ------------------------------
100 10 MARK JOHNSON
200 20 TOM KYTE
300 20 SEAN DILLON

-----------------------------

...and there you have it. Hope that helps!



How can I handle this? One row for the department table and 3 rows for the 
employee table in the same XML row.
<?xml version = "1.0"?>
<ROWSET>
<ROW num="1">
<DEPTNO>10</DEPTNO>
<DNAME>SALES</DNAME>
<EMPLOYEE>
<EMPNO>100</EMPNO>
<ENAME>MARK JOHNSON</ENAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>200</EMPNO>
<ENAME>VICTOR JAEN</ENAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>300</EMPNO>
<ENAME>JHON SMITH</ENAME>
</EMPLOYEE>
</ROW>
</ROWSET>

Thanks a lot!

Followup:
 
1* 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
scott@ORA920> /

DBMS_XMLGEN.GETXML('SELECTDEPTNO,DNAME,CURSOR(SELECTEMPNO,ENAMEFROMEMPWHEREEMP.D
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<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>