XML in the Database: A Brief Overview

Oracle provides a lot of XML functionality in the database. This entry will cover the different tools Oracle provides and future entries will get into the details of each.

The three primary XML tools that oracle provides are: XML Developers Kit (XDK), XML DB and SQL Extensions for XML (SQLX and XMLType). Below is a definition of each.

XDK

The XDK is a complete XML development toolkit that was introduced for 8i. I think the first version was labeled XDK9i but the toolkit actually was available for 8i. I remember using the beta product. 😉

XDK has everything you need for developing complete XML applications in the database. It supports DOM (tree access) and SAX (streaming access); and JAXP if you prefer implementation independence. It also has an XSLT processor.

The XDK also comes with the XML SQL Utility. XSU is the API allowing creation and storage of XML. DBMS_XMLQuery.getXML accepts a query as a parameter and returns an XML document.

DBMS_XMLSave.InsertXML allows a program to insert XML documents into tables and columns. The XML must conform to some limitations, i.e. element tags must match column names. That is somewhat configurable. You can also update data with XML documents by using DBMS_XMLSave.UpdateXML and delete by using DBMS_XMLSave.DeleteXML.

Personally, I believe that there are better ways to extract XML from the database than DBMS_XMLQuery and better ways to get it in than DBMS_XMLSave. If you’re using Java or C and you are presenting XML on the web, the XDK is the way to go but if you’re using PL/SQL, I really think Oracle made it more complex than it needs to be.

The XDK also provides the XSQL servlet which is a nice, web based XML access and presentation utility and they provide a SOAP framework. Both of these fall under the XDK header. XSQL is basically a utility where you pass in a SQL query and an XSLT document and it will present it on the web. It is somewhat programmable but I found it too limited for real application use. For a simple in house reporting solution or something along those lines, it is functional enough.

XML DB

XML DB is an XML repository for XML. This repository gives high performance access to XML documents and let’s you store those documents in a seemingly unstructured format (but is really relational under the covers). XML DB ties in well with the XDK to create Java applications that create and store XML documents.

With XML DB you have a folder (directories, sub directories, etc) view of your documents. You can grant permissions to documents as you would on a file system and can even keep versions of documents.

Oracle has added FTP and HTML access to the database to support XML DB. You can configure listeners to listen on those ports and have the database act as a server.

XML DB is a great tool when you need to save, modify and present XML content. The modify word is the important part. If all you are doing is generating and presenting XML, there is no reason to store it as XML. If the data is already XML and you are just presenting it, there is no reason to put it in a database. If you have XML and need to add to it, update it or delete some of it before presenting it, or maybe even merge documents selectively (think where clause), XML DB is where you want to do it. What makes XML DB nice it the choice to use it as XML or to access and modify it via SQL.

The power of XML DB, in my opinion, is the support of advanced SQL XML queries and the new XMLType. I use these tools on a day-to-day basis.

SQL Extensions for XML

The XMLType is an OO XML aware data type. It can be used in columns or in PL/SQL just like VARCHAR2 or DATE. XMLType has member functions that allow access to data using XPath.

A quick example extracting a specific value from an XML varchar2 string:

DECLARE

  v VARCHAR2(32000) := '<DATA><LINE>A</LINE><LINE>B</LINE><LINE>C</LINE></DATA>';
  
  x XMLType;
  
BEGIN

  x := XMLType(v);
  
  DBMS_OUTPUT.put_line(
     x.extract('/DATA/LINE[1]').getStringVal()
                 );
                         
END;

If you read my previous entries on OO, you can see that XMLType is an Object type. The first line after the begin is the XMLType constructor. In the DBMS_OUTPUT, I’m extracting the first child node and displaying it as a string. XMLType has many useful built-in functions. I only dreamed of such usefulness in 8i. At times, in 8i, manipulating XML was down right painful.

SQLX is a set of SQL functions that give a programmer the ability to generate XML documents and fragments. There are a handful of new XML* functions described in the SQL reference. The ones I’m finding to be most useful are XMLElement, XMLAGG and XMLForest.

Here is a quick sample using SQLX:

SQL> SELECT 
  2      XMLElement("Ename",ename ) Ename,
  3      XMLElement("Empno",empno ) Empno
  4    FROM EMP
  5    WHERE rownum < 5  
  6  /

ENAME
------------------------------------------
EMPNO
------------------------------------------
<Ename>SMITH</Ename>
<Empno>7369</Empno>

<Ename>ALLEN</Ename>
<Empno>7499</Empno>

<Ename>WARD</Ename>
<Empno>7521</Empno>

<Ename>JONES</Ename>
<Empno>7566</Empno>

SQL> SELECT 
  2      XMLElement("Employees",
  3     XMLForest( ename, empno ) )
  4    FROM EMP
  5    WHERE rownum < 5  
  6  
SQL> /

XMLELEMENT("EMPLOYEES",XMLFOREST(ENAME,EMPNO))
---------------------------------------------------------------
<Employees><ENAME>SMITH</ENAME><EMPNO>7369</EMPNO></Employees>
<Employees><ENAME>ALLEN</ENAME><EMPNO>7499</EMPNO></Employees>
<Employees><ENAME>WARD</ENAME><EMPNO>7521</EMPNO></Employees>
<Employees><ENAME>JONES</ENAME><EMPNO>7566</EMPNO></Employees>

And that’s just a small part of the power of SQLX. The Oracle 10g SQL reference and XML DB manuals are great resources on this. This overview is the segue to my next article, The Sorcerer of OO Part 4, where I will combine XML and Object Views. In the coming weeks I will also write in more detail how to, and when to, use XML in the database. I will cover SQLX and XMLType in painful detail.

Thanks,

Lewis

Technorati : , , , , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.