As Published In
May/June 2003
|
Technology XML
SQL in, XML out
By Jonathan Gennick
New SQL/XML functions provide a standards-based bridge between relational data and XML.
For decades businesses have been pouring their data into relational databases. If you're trading data with another business, however, it's more and more likely that you'll need to pull data out of your relational database and format that data as XML before transmitting it to your business partner.
The SQL/XML Standard
Oracle9i Database implements a number of standards-based functions enabling you to query relational data and return XML documents. These functions collectively fall under the heading of SQL/XML, sometimes referred to as SQLX. SQL/XML is now an emerging part (Part 14, to be precise) of the ANSI/ISO SQL standard and is on track to be published as an ISO/IEC standard later this year. The SQL/XML standard has broad industry support; major database vendors involved in the effort to define the SQL/XML standard include IBM, Microsoft, Oracle, and Sybase.
The Final Draft International Standard for SQL/XML defines the
following elements:
- XML: a datatype to hold XML data
- XMLAgg: a function to group, or aggregate, XML data in GROUP BY queries
- XMLAttributes: a function used to place attributes in XML elements returned by SQL queries
- XMLConcat: a function to concatenate two or more XML values
- XMLElement: a function to transform a relational value into an XML element, in the form: <elementName>value</elementName>
- XMLForest: a function to generate a list, called a "forest," of XML elements from a list of relational values
- XMLNamespaces: a function to declare namespaces in an XML element
- XMLSerialize: a function to serialize an XML value as a character string
From this list, Oracle9i Database implements the following: the XML datatype (as XMLType), XMLAgg, XMLConcat, XMLElement, and XMLForest. Support for the other functions is planned in future releases.
In addition to the functions and the datatype, the SQL/XML standard defines rules for transforming column names into XML element names and for transforming SQL datatypes into XML datatypes. These rules are applied automatically by XMLElement and the other SQL/XML functions.
Creating an XML Document
Suppose that you work for a tourist bureau and that you have lists of tourist attractions stored relationally, as in the tables shown below:
SQL> describe COUNTY
Name Null? Type
-------------- ----- --------------
COUNTY_NAME VARCHAR2(10)
STATE VARCHAR2(2)
SQL> describe ATTRACTION
Name Null? Type
-------------- ----- --------------
COUNTY_NAME VARCHAR2(10)
ATTRACTION_NAME VARCHAR2(30)
ATTRACTION_URL VARCHAR2(40)
GOVERNMENT_OWNED CHAR(1)
LOCATION VARCHAR2(20)
You've just been asked to feed your data to a similar bureau in another state, and the feed needs to be in XML. To begin, you can make use of XMLElement to generate an XML element for each tourist attraction, as shown below:
SELECT XMLElement("Attraction",
attraction_name)
FROM attraction;
XMLELEMENT("ATTRACTION",ATTRACTION_NAME)
------------------------------------------
<Attraction>Pictured Rocks</Attraction>
<Attraction>Da Yoopers ... </Attraction>
<Attraction>Valley Camp ...</Attraction>
...
XMLElement is one of the most important SQL/XML functions to understand, because creating XML elements is the fundamental reason for SQL/XML's existence. The first argument to XMLElement is an identifier, not a string argument; it's an identifier in the same way that a table name or a column name is an identifier, hence my use of double-quotes in the XMLElement query above. If you want a lowercase column name in a table, use double quotes around that column name when creating the table. Likewise, if you want a lowercase element name, enclose it within double quotes. My second argument to XMLElement is the column name that provides the value for the element I'm creating.
For the data feed, you want not just attraction names but other information
as well. You can generate subelements
for each attraction by nesting calls to XMLElement, as shown in Listing 1. The outer call to XMLElement generates the <Attraction> element. The inner calls to XMLElement generate the nested <Name>, <Location>, and <URL> elements. Notice the use of XMLAttributes as the second argument to the outer XMLElement function call. An invocation of XMLAttributes
is an optional second argument to XMLElements, and in this case it results in the GOV attribute that you see in each <Attraction> tag.
It's important to understand that the XMLElement function returns an XMLType value, not a character string value. You see characters in the listings because SQL*Plus (in Oracle9i Release 2) implicitly extracts and displays the XML
text for any XMLType value you select. If you want to use SQL*Plus to reproduce the examples in this article, you can download the table creation scripts
here. In SQL*Plus, issue the command SET LONG 2000, so that XML output is not truncated, and optionally issue the command SET PAGESIZE 80, to avoid annoying page breaks in the XML output.
Dealing with Possible Null Elements
Whenever you're working with relational data, you must consider the possibility of encountering null values. What if, for example, the LOCATION column is null for some attractions? Using XMLElement, a null column value results in an empty element, as shown for the first query and result in Listing 2. If you don't want such empty elements in your XML, there are at least two solutions.
One solution you can use to avoid creating empty XML elements is to use a SQL CASE expression to test a column for null values, and to return either null or the results of XMLElement, as appropriate. The second query in Listing 2 implements this approach. Each CASE expression ensures a null result when the column in question is null, and it passes non-null values to the inner XMLElement functions. The outer XMLElement pulls together any non-null values, and any nulls that would otherwise become empty XML elements are ignored.
Another way to avoid creating empty XML elements is to use the XMLForest function. In XML terms, the elements nested underneath <Attraction> can be referred to as a "forest." XMLForest enables you to generate a forest of elements with just one function call. When generating those elements, XMLForest skips elements with null values. You can see this in the output from the following query, where XMLForest does not produce the null <location> element in the query result:
SELECT XMLElement("Attraction",
XMLAttributes(government_owned AS GOV),
XMLForest(attraction_name AS "Name",
Location AS "Location",
attraction_url AS "URL"))
FROM attraction
WHERE attraction_name='Mackinac Bridge';
XMLELEMENT("ATTRACTION",XMLATTRIBUTES(GOV
-------------------------------------------------
<Attraction GOV="Y">
<Name>Mackinac Bridge</Name>
<URL>http://www.mackinacbridge.org/</URL>
</Attraction>
This query and its one call to XMLForest is definitely easier to type and leaves less room for typing errors than the three calls to XMLElement shown in Listing 2. In both cases, null elements are eliminated from the query result. The disadvantage of using XMLForest is that you cannot specify element attributes. If you need to specify attributes for an element, you must use XMLElement in conjunction with XMLAttributes.
Aggregating XML Elements
The previous query produces a separate XML document for each attraction. That's not a very realistic scenario. If you were feeding data on attractions to a business partner, you'd likely want to aggregate the data in some manner. For example, you might wish to collect all the attractions for a single county together and transmit them in one document. You can do that by using the XMLAgg function in conjunction with a GROUP BY query.
XMLAgg is an aggregate function,
just like MIN, MAX, and AVG. The key to using it is to group your data on some common value. The query in Listing 3 groups data by county name. The XMLAgg function then takes all the individual <Attraction> elements for a given county, concatenates them together,
and returns them as a single XMLType value. That value then feeds into a new, enclosing XMLElement function call that generates the <County> element. The result is that the query in Listing 3
In Listing 3, you see XMLAttributes being used to generate multiple
attribute values. The outermost call to XMLAttributes generates three attributes: one for the county name and two pointing to the XML schemas to which the document conforms. Because the query
is a GROUP BY, the outermost XMLElement function call and its associated XMLAttributes call can refer only to summarized columns. Try using a.county_name instead of c.county_name, and you'll get an error because a.county_name is not a GROUP BY expression.
Foldering an XML View
One particularly exciting XML-related feature in Oracle9i Release 2 is that you can use a SQL/XML query such as I've been developing in this article as the basis for creating an XMLType view. You can then "folder" that view so that its contents appear as XML files in a directory in the XML DB Repository.
Whenever you open one of those XML files using Internet Explorer or an application such as Microsoft Excel, the file's contents are constructed on the fly by executing the query to create that particular XMLType view. The Microsoft Office XP version of Microsoft Excel supports an XML file format, so if you generate an XMLType view using the XML schema that Excel expects, you can open spreadsheets containing current data, fresh from the database.
Listing 4 shows an XMLType view made from the query developed in this article. (Note that this particular CREATE VIEW statement and the foldering feature work only in Oracle9i Database Release 9.2.0.2 or higher.) The OBJECT ID clause in the CREATE VIEW statement generates a unique object identifier for each row in the view. The view returns one XML document (one row) per county, with that document listing all of that county's attractions. The reference to sys_nc_rowinfo$ that you see in the extractValue function is a reference to the "current" row in the view. The XPath query syntax '/County/@Name' causes extractValue to return the value
of the Name attribute in each <County> element, which is then used as the basis for generating a unique identifier for
each row returned by the view.
The view alone is not enough to make the XML documents appear in the repository. It's also necessary to create repository resources, which show up as documents, and to tie each resource to an object identifier. The PL/SQL block in Listing 5 does this for the attraction_xml view. Note that it's not even necessary to query the view. The code in Listing 5 opens a cursor to retrieve all distinct county names from the attraction table on which the view is based. A call to the MAKE_REF function then generates a REF to each county's row in the view's result set. Finally, this REF is used in a call to DBMS_XDB.createResource. The createResource function creates a resource in the XML DB Repository and links that resource to the data for the given county in the view's result set. The resource appears as a .xml file in the repository.
Note that before running the code in Listing 5, you need to create a folder named /ATTRACTIONS in your repository. It's in that folder that the PL/SQL code creates the XML documents that refer to the view.
Keep Learning!
Oracle's support for SQL/XML in Oracle9i Database makes it very easy to generate XML data from your existing relational data. Using SQL/XML
functions, you can easily create XML elements, nested XML elements, aggregations of XML elements, and more. The built-in XMLType datatype enables you to work natively with XML in the database. SQL/XML-based views, the XML DB Repository, and foldering make a powerful combination that enables you to make data directly available via XML for use in applications such as Microsoft Excel. If you plan to generate XML from relational data in the future, or even just think you might do so, take time to become familiar with XMLType and with the various SQL/XML functions.
Jonathan Gennick (Jonathan@Gennick.com) is an experienced Oracle DBA and an Oracle Certified Professional. He likes to explore new Oracle technologies and recently completed work on his Oracle SQL*Plus Pocket Reference, Second Edition (O'Reilly & Associates, 2002).
| 
Bookmark
