Using DataDirect XQuery™ with Relational Databases

| | Learn More

DataDirect XQuery can query relational and XML data stored in relational databases. When querying relational data, tables are accessed as virtual XML documents. This is particularly useful for XML Publishing and Reporting or for Data Integration. DataDirect XQuery supports most relational databases.

Relational databases often hold massive amounts of data, so DataDirect XQuery uses a very efficient way to query them - but hides such complexity from the query writer:

• To the XQuery, a table just looks like an XML document.
• To the relational database, an XQuery is just another (set of) SQL query(ies) to run efficiently.

Data is not extracted in order to query it; instead, efficient SQL is generated to retrieve just the data needed to construct the results for a given XQuery. Retrieval of the results is performed lazily, driven by how the application is consuming the XQuery result.

XQuery for Relational Databases
DataDirect XQuery translates an XQuery that uses relational data into SQL, executes the SQL, and returns the results in the format specified by the XQuery. This means that very large databases can be queried efficiently, using the indexes and query optimization of the database. Lazy result retrieval also ensures that large results can be efficiently handled by the application running the XQuery. The techniques that DataDirect XQuery uses to generate efficient SQL and implement XQuery for relational databases are discussed in DataDirect XQuery Performance: Generating SQL.

DataDirect XQuery and Relational Databases Example
In DataDirect XQuery, queries address relational tables with the fn:collection() function, typically using the name of a database table as an argument:

collection('HOLDINGS')

The argument can also contain the database catalog or database schema in addition to the database table name:

collection('FINANCIAL.JOSEPH.HOLDINGS')

The XML view of the database represents a table as a sequence of elements that correspond to rows - each of these row elements contains one element per column. For instance, the first few rows of the HOLDINGS table might look like this in the XML view:

			<HOLDINGS>
        <USERID>Jonathan</USERID>
        <STOCKTICKER>AMZN</STOCKTICKER>
        <SHARES>3000</SHARES>
      </HOLDINGS>
      <HOLDINGS>
        <USERID>Jonathan</USERID>
        <STOCKTICKER>EBAY</STOCKTICKER>
        <SHARES>4000</SHARES>
      </HOLDINGS>

An XQuery that uses this table treats the table just like an XML document. For instance, we can get holdings in AMZN stock with the following query.

for $h in collection('HOLDINGS')/HOLDINGS
      where $h/STOCKTICKER eq 'AMZN'
      return $h

Similarly you can easily join XML and relational data in a single XQuery. for instance, we can get holdings in all stocks listed in a "stocks.xml" document with the following query.

for $t in doc("stocks.xml")/stocks/stock/ticker
       for $h in collection('HOLDINGS')/HOLDINGS
        where $h/STOCKTICKER eq $t
        return $h