The Oracle XMLType Data Type

Creating XML documents in any specific database is often all about using the specialized tools available within that database engine. Oracle Database is no exception to this rule. The first thing to do with an Oracle database is to examine what is actually a specialized data type: the XMLType data type.

In its most basic form, XML in Oracle SQL comprises the XMLType data type and a number of methods attached to that data type. (The XMLType is effectively a class.) The XMLType stores the text of XML documents and allows access to the XML document object model. The document object model allows access to all the elements in an XML document programmatically.

The XMLType data type is a specialized data type used for both storing and processing of XML documents in an Oracle database. Additionally, an XMLType can be stored in a specially created table in a binary object field called a CLOB object. A CLOB object in an Oracle database is essentially an internally searchable text object. This means you can search for a single piece of text within the binary object.

XMLTypes are added into tables in Oracle Database in the following ways:

Create a table that is an XML data type in itself:

   CREATE TABLE XMLDOCUMENT OF XMLTYPE;   

Or create a table containing an XMLType data type field as follows :

   CREATE TABLE XML (     ID NUMBER NOT NULL, XML XMLTYPE     ,CONSTRAINT XPK_XML PRIMARY KEY (ID) );   

XMLType data types can even be used in the Oracle database programming language PL/SQL (Programming Language for SQL).

PL/SQL in Oracle Database provides for extensive programming control capabilities in the creation of stored procedures, stored functions, and triggers within the database.

   DECLARE    XML XMLTYPE; BEGIN    NULL; END; /   

Oracle XMLType Data Type Methods

There are numerous methods that can be executed against XMLType data types. Oracle Database calls these methods subprograms . I am calling them methods because they are executed against iterations of the XMLType data type. In other words, these methods are executed something like this:

   DECLARE    XML XMLTYPE; BEGIN    XML = SELECT * FROM XMLDOCUMENT;  print  XML.GETSTRINGVALUE(); END; /   

The preceding script is pseudocode. This means it will not function as is in any programming lan guage, in any database, or on any computer.

Assume in the preceding script that the print statement dumps whatever literal string indicated directly to the output.

This is a brief summary of a few of those methods:

• EXISTSNODE(XPath-expression, [ namespace ]) : Uses an XPath expression to see if a node exists in an XMLType. It returns 1 (true) if the node is found, otherwise 0 (false).

XPath is a specialized XSLT tool for parsing XML documents and is discussed later on in this book. An XPath expression is essentially a pattern that can be potentially matched within an XML docu ment. XPath is a specialized scripting language used to scan XML documents.

• EXTRACT(XPath-expression) : Uses an XPath expression to extract a node (including all children) from an XMLType.

Executing the EXTRACT function as XMLType.EXTRACT('/*') will produce a much more readable display for XML objects.

• GETNAMESPACE() : Retrieves the namespace of an XMLType object.

• GETROOTELEMENT() : Retrieves the root element of an XMLType object.

• GETSTRINGVAL() : This method is required in order to convert the XML structure (stored in an XMLType) into a readable string.

• ISFRAGMENT() : A fragment of an XML document contains no root element (or multiple root elements). Thus 1 (true) is returned for a fragment, 0 (false) for a properly formed XML document.

• TRANSFORM(XSL-stylesheet-script) : Transforms an XMLType with an XSL stylesheet into a transformed XML document.

You do not need to understand exactly what all these methods do at this point. You just need to know that they exist. You should remember from Chapter 3 how XSL is used to scan through XML documents and apply processing to parts of those XML documents. Of particular note are XSL node creation and templating element categories, which are similar in function to the preceding methods.

You dont need to know anything more about the XMLType data type itself. It is simply a structure for storing an XML document, where the XML document can be worked with as an XML document. In other words, the XMLType data type allows the XML document stored within it to be accessible as any other XML document would be. This includes access to the XML DOM, all sorts of specialized XML features such as XSL and even standardized database access structures such as XQuery.

XQuery is a standard set of tools used for database access of XML documents with any database. It is thus not appropriate for this chapter, which focuses on Oracle Database alone. XQuery is covered from a more generic ( non-database vendor-specific) perspective later on in this book.

In short, the XMLType data type has little to do with processing an XML document, or even with SQL in an Oracle database.

All database vendors such as Oracle (Oracle Database), Microsoft (SQL Server), or IBM (DB2), imple ment a standardized form of SQL. However, they do make changes that are specific to each database engine.