The XMLType Type

XML (Extensible Markup Language) is fast becoming a very important technology to understand. Did we say "fast becoming"? Strike that—XML is important right now, and the predefined object type XMLType enables you to store XML data in an Oracle database and manipulate that XML data from within SQL and PL/SQL.

XML is a huge subject that we can't hope to cover in detail. Instead, we will settle for familiarizing you with XMLType so that you understand how important it is and what you can do with it. To learn more about using XML with Oracle, we recommend Steve Muench's book, Building Oracle XML Applications (O'Reilly). To learn more about XML in general, you might try Learning XML by Erik T. Ray (O'Reilly).

In Oracle9i Release 1, you need to use the "SYS." prefix when referencing the XMLType object type. Release 2 allows synonyms to point to object types, and the database creation script ($ORACLE_HOME/rdbms/admin/dbmsxmlt.sql) that creates XMLType now also creates the public synonym XMLTYPE, which points to the SYS.XMLType predefined object type.

Using XMLType, you can easily create a table to hold XML data:

CREATE TABLE falls ( fall_id NUMBER, fall SYS.XMLType);

The fall column in this table is of XMLType and can hold XML data. To store XML data into this column, you must invoke the static CreateXML method, passing it your XML data. CreateXML accepts XML data as input and instantiates a new XMLType object to hold that data.

The new object is then returned as the method's result, and it is that object that you must store in the column. CreateXML is overloaded to accept both VARCHAR2 strings and CLOBs as input.

Use the following INSERT statements to create three XML documents in the falls table:

INSERT INTO falls VALUES (1, XMLType.CreateXML( '<?xml version="1.0"?> <fall> <name>Munising Falls</name> <county>Alger</county> <state>MI</state> <url> http://michiganwaterfalls.com/munising_falls/munising_falls.html </url> </fall>')); INSERT INTO falls VALUES (2, XMLType.CreateXML( '<?xml version="1.0"?> <fall> <name>Au Train Falls</name> <county>Alger</county> <state>MI</state> <url> http://michiganwaterfalls.com/autrain_falls/autrain_falls.html </url> </fall>')); INSERT INTO falls VALUES (3, XMLType.CreateXML( '<?xml version="1.0"?> <fall> <name>Laughing Whitefish Falls</name> <county>Alger</county> <state>MI</state> </fall>'));

You can query XML data in the table using various XMLType methods. the existsNode method used in the following example allows you to test for the existence of a specific XML node in an XML document. The built-inSQL EXISTSNODE function, also in the example, performs the same test. Whether you use the method or the built-in function, you identify the node of interest using an XPath expression.^[2]

^[2] XPath is a syntax used to describe parts of an XML document. Among other things, you can use XPath to specify a particular node, or attribute value, in an XML document.

Both of the following statements produce the same output:

SQL> SELECT fall_id 2 FROM falls f 3 WHERE f.fall.existsNode('/fall/url') > 0; SQL> SELECT fall_id 2 FROM falls 3 WHERE EXISTSNODE(fall,'/fall/url') > 0; FALL_ID---------- 1 2

You can, of course, also work with XML data from within PL/SQL. In the following example we retrieve the fall column for Munising Falls into a PL/SQL variable that is also of XMLType. Thus, we retrieve the entire XML document into our PL/SQL program, where we can work further with it. After retrieving the document, we extract and print the text from the /fall/url node.

<<demo_block>>DECLARE fall XMLType; url VARCHAR2(80);BEGIN --Retrieve XML for Munising Falls SELECT fall INTO demo_block.fall FROM falls f WHERE f.fall_id = 1; --Extract and display the URL for Munising Falls url := fall.extract('/fall/url/text( )').getStringVal; DBMS_OUTPUT.PUT_LINE(url);END;

We'd like to call your attention to the following two lines:

SELECT fall INTO demo_block.fall

Our variable name, fall, matches the name of the column in the database table. In our SQL query, therefore, we qualify our variable name with the name of our PL/SQL block.

url := fall.extract('/fall/url/text( )').getStringVal;

To get the text of the URL, we invoke two of XMLType's methods:

extract

Returns an XML document, of XMLType, containing only the specified fragment of the original XML document. Use XPath notation to specify the fragment that you want returned.

getStringVal

Returns the text of an XML document.

In our example, we apply the getStringVal method to the XML document returned by the extract method, thus retrieving the text for the Munising Fall's URL. The extract method returns the contents of the <url> node as a XMLType object, and getStringVal then returns that content as a text string that we can display.

You can even index XMLType columns to allow for efficient retrieval of XML documents based on their content. You do this by creating a function-based index, for which you need the QUERY REWRITE privilege. The following example creates a function-based index on the first 80 characters of each falls name:

CREATE INDEX falls_by_name ON falls f ( SUBSTR( XMLType.getStringVal( XMLType.extract(f.fall,'/fall/name/text( )') ),1,80 ) );

We had to use the SUBSTR function in the creation of this index. The getStringVal method returns a string that is too long to index, resulting in an ORA-01450: maximum key length (3166) exceeded error. Thus, when creating an index like this, you need to use SUBSTR to restrict the results to some reasonable length.

If you decide to use XMLType in any of your applications, be sure to consult Oracle's documentation for more complete and current information. XMLType was introduced in Oracle9i Release 1. Oracle added a great deal of XML functionality in Oracle9i Release 2, and if you are using Release 2 you should take the time to become familiar with the latest XML capabilities. Oracle's Application Developer's Guide—XML is an important, if not critical, reference for developers working with XML. The SQL Reference also has some useful information on XMLType and on the built-in SQL functions that support XML.