Top-Level XQuery

Top-level XQuery provides a way to query collections of XML documents and forests, and XML views of relational data without the need to use SQL. It becomes the top-level language that is used as the primary query language at the same level as SQL is being used for relational data. It basically subsumes SQL under the XML and XQuery model and enables XML-savvy users to operate on both relational and XML data without having to use SQL. Unlike queries on an XML datatype, XQuery expressions can combine different XML documents and XML views of relational data. Another advantage of having top-level XQuery expressions is the ability to provide finer-grained concurrency control and locking (such as node-level tree locking).

Many relational database systems and tools providers offer this approach as a mid- tier extension, providing views over the relational data and an XQuery data integration engine (see Chapter 6). However, if a relational database system already provides an XML datatype, then it seems straightforward to extend the programming model to provide top-level XQuery support over a collection of XML instances and XML views of relational data. In the following, we use the keyword XQUERY to indicate a top-level XQuery and enclose the XQuery expression in curly braces. Such an expression returns either a single instance of the relational XML datatype or (depending on the relational database system's programming model) a single-column/single-row table or a singleton collection of XML.

XML Document (Fragment) Collections

There are two ways to provide ( potentially typed) collections of XML data. One approach simply uses an XQuery function in the top-level XQuery expression to refer to a table column that is typed as XML . The XQuery collection() function could be used to refer to such a column, if the relational system provided a mapping of the relational names into the URI space. Alternatively, the database could provide a function sql:collection($c as xs:string) as document* that refers to the column and returns a sequence of the extended document root nodes. An example based on the table specified in Listing 7.4 is given below. If the column contains typed XML, then the static type information can be used to type the top-level XQuery.

 XQUERY {   declare namespace po = "http://www.example.com/po-schema"   sql:collection('Customers.PurchaseOrders')/po:po/po:details } 

Another approach is to extend the notion of a table to allow the creation of a table of type , where the type would not be a rowtype but the XML datatype. The example in Listing 7.9 first defines a collection of purchase-order instances statically constrained by the purchase-order schema; then it provides two alternate ways to populate it (one using an SQL INSERT statement and one using a pseudo-XQuery-like syntax), and finally it queries the collection.

Listing 7.9 Example of an XML Collection Using CREATE TABLE OF type and a Top-Level XQuery

 CREATE TABLE PurchaseOrders OF TYPE XML TYPED AS POSchema INSERT INTO PurchaseOrders SELECT PurchaseOrders FROM Customers XQUERY-MODIFY {   insert     <po:purchase-order           xmlns:po="http://www.example.com/po-schema" id="2001">          <po:shipTo>            <po:address>42 2nd Avenue</po:address>            <po:city>Bellevue</po:city>            <po:state>WA</po:state>            <po:zip>98006</po:zip>          </po:shipTo>          <po:details qty="4" price="3.44"/>        </po:purchase-order>   as last into     sql:collection('PurchaseOrders') } XQUERY {   declare namespace po = "http://www.example.com/po-schema"   sql:collection('PurchaseOrders')/po:purchase-order/po:details } 

XML Views over Relational Data

Besides providing access to collections of XML instances, top-level XQuery expressions should also provide access to XML views over relational data. As described in Chapter 6, there are different view mechanisms.

Since the SQL/XML part of the ISO SQL-2003 standard provides two general forms of default mappings of relational tables to XML [SQLXML], relational systems will most likely provide at least one of them if they provide XQuery access to relational data. They may also provide support for other mappings, such as an annotated schema-driven view mechanism.

The SQL/XML relational-to-XML mapping provides two main mapping approaches: The table-to-doc approach maps every table to an XML document where the root element is named according to the table name . Every row is mapped to an element named row underneath that root element and contains the column data of the row as subelements, where the names of the subelements are based on the column names. The table-to-forest approach maps a table into an XML element forest where every row is mapped to an element whose name is based on the table name. As in the other mapping, the column data is mapped to subelements. Both approaches have additional mapping options, such as an option to map columns containing the relational NULL values to either absent elements or to elements marked with xsi:nil="true" . The table-to-doc approach is well suited for XML-based relational-to-relational data transport, whereas the table-to-forest approach is better suited for querying the relational data with XQuery, since it avoids the additional step expressions introduced by the artificial row elements.

Listing 7.10 gives an example of an XML view of the relational table in Listing 7.2 that uses the table-to-forest approach. Whitespace has been added to help in formatting.

Listing 7.10 Example of an XML View of a Relational Table

 <ORDER>   <ID>4023</ID>   <ORDERDATE>2001-12-01</ORDERDATE>   <PODETAIL>     <purchaseOrder xmlns=?http://po.example.com?>       <originator billId=?0013579?>         <contactName>Fred Allen</contactName>         <contactAddress>           <street>123 2nd Ave. NW</street>           <city>Anytown</city>           <state>OH</state>           <zip-four>99999-1234</zip-four>         </contactAddress>         <phone>[phone]</phone>         <originatorReferenceNumber>AS 1132</originatorReferenceNumber>       </originator>       <order>         <item code=?34xdl 1278 12ct? quantity=?1?/>         <item code=?57xdl 7789? quantity=?1? colorcode=?012?/>       </order>       <shipAddress sameAsContact=?true?/>       <shipCode carrier=?02?/>     </purchaseOrder>   </PODETAIL> </ORDER> <ORDER>   <ID>5327</ID>   <ORDERDATE>2002-04-23</ORDERDATE>   <PODETAIL>     <purchaseOrder xmlns=?http://po.example.com?>        <originator ...   </PODETAIL> </ORDER> ... 

The top-level XQuery environment can be given access to these views in various ways. Generally this is done either through the existing doc() and collection() functions, where the location and mapping information is encoded in the URI, or possibly through additional built-in functions.

Any of the views can be represented as a single document by making the view's top-level element(s) become the children of a document node. The result will not necessarily correspond to a well- formed XML document, but is allowed by the XQuery data model. In this case, the system must either specify a URI encoding of the view parameters for use with the XQuery doc() function or define a new, built-in XQuery function. For example, top-level XQuery could provide the following built-in XQuery function:

 sql:table($table as xs:string           [, $table_map_option as xs:string           [, $null_map_option as xs:string]]) as element* 

This function returns the view of the given table $table with the optional parameters indicating the preferred table- and null-mapping options. For instance, sql:table('Order', 'table-to-forest', 'xsinil') would return the view of Listing 7.10.

Alternatively, the system could also provide access to the views as a collection of individual document nodes, one for each top-level element. In that case, the system would have to provide a function sql:collection() or define a URI mapping for use with XQuery's built-in function fn:collection() . In any case, the view will probably not guarantee a deterministic order of the top-level elements, because relational tuples have no implicit order. If a relational system provides other relational-to-XML mapping techniques, such as Microsoft SQL Server's annotated schema views [RYS2001], additional functions can be provided to expose them in top-level XQuery expressions.

Turning to implementation, we note that the actual physical mapping of the XQuery expressions against the views ”regardless of how they are accomplished ”can be done directly against the relational engine's logical algebra operations. This means that an implementation can add new logical operations as needed to express specific semantics not available otherwise . This ability allows an easier mapping than the mid-tier approach, which must either map the query into an SQL statement or ship more data than needed to the mid-tier and post-process the data to get the correct execution semantics.

Any of these view-generation techniques present a major problem for data modification statements. Since they assume a node-based data model, where every node has a unique identification, mapping them into SQL-based update statements on relational tables that may not have such a unique identification requires a careful design of the update propagation through the view.