Home:XQuery Engine:Features:SQL Server XML

Microsoft SQL Server Support in DataDirect XQuery

Microsoft SQL Server is best supported by the DataDirect XQuery® processor. DataDirect XQuery® allows you to query relational tables in the same way as XML documents, making it easy to publish both relational and XML data as XML. DataDirect XQuery® is particularly useful for XML publishing and reporting, and for data integration. DataDirect XQuery® runs on any Java platform, and is easily embeddable into almost any Java program. DataDirect XQuery® works with any J2EE Application Server, although no application server is required.

Following are just some of the reasons why DataDirect XQuery® is the best XQuery processor for querying relational data stored in SQL Server databases, including SQL Server 2000 and SQL Server 2005 (Yukon).

Efficient Query Execution via SQL and JDBC

MS SQLServer databases often hold massive amounts of data, so DataDirect XQuery® uses a very efficient way to query them — but hides this from the query author. Using DataDirect XQuery®:

• To the XQuery query, a table looks like an XML document
• To the relational database, an XQuery looks like efficient SQL

Data is not extracted in order to query it; instead, DataDirect XQuery® generates SQL and executes that SQL on the server and is streamed back to the caller via high-speed JDBC drivers, enabling you to retrieve just the data needed to construct the results for a given XQuery.

Microsoft SQLServer Optimizations

One key advantage of DataDirect XQuery® over other XQuery processors is that it decomposes your XQuery and translates it to SQL. The benefits of such a translation, of course, are enormous, especially in terms of the performance and efficiency with which your XQuery applications will run. Once converted to SQL, the query is pushed to the database server and executed there. In the database environment, the SQL is able to take advantages of your database's indexes and primary key/foreign key relationships, speeding data access and retrieval.

Other XQuery processors are unable to convert XQuery to SQL, leaving them with no choice but to fetch the entire table from the relational database and return it to the client side, where it is then converted, in its entirety, to XML. This might work fine with trivial queries or very small relational tables, but it is not an approach that scales, or even one that offers reasonable performance for real-world data integration and aggregation.

XML Optimizations

Query results are returned to DataDirect XQuery® as tabular data, which is then easily converted to XML. Once converted to XML, DataDirect XQuery® uses data streaming and document projection technologies to focus on only those elements and attributes that are relevant to the query, minimizing in-memory processing. This approach allows for fast and efficient processing of very large XML documents (1 gigabyte and larger!). Relational joins and other complex query structures pose no problem for the DataDirect XQuery® processor.

Easy Microsoft SQL Server Database Connectivity

It's easy to Configure and connect to a SQL Server server. You can use an XML IDE like DataDirect's Stylus Studio to quickly define server configurations and connect to that server, using a simple dialog box like the one shown here:

And it's easy to create and reuse default database connections, too.

Even without an XML Editor like Stylus Studio, defining SQL Server database connections is easily accomplished using the XQuery API for Java (XQJ) which is like a JDBC for XML. XQJ interfaces are implemented by DataDirect XQuery®. You simply need to define data source and connection expressions, like the ones shown in the following code sample, in your Java application, and you're all set.

Transparent Access to Relational Data as XML

Once you have connected to a relational data source via JDBC (a Java-version of ODBC), Stylus Studio, and the Editor for Eclipse makes it easy for you to automatically create XQuery code. Using Stylus Studio, for example, you just drag and drop a table from the File Explorer window, exposing all the tables from that tablespace as potential data sources. Next, drag the table you want to query and drop it on the XQuery Source tab — Stylus Studio automatically creates perfect XQuery code based on the table you choose. These easy steps are summarized in the following illustration.

And when you're done building and testing your XQuery code, you can quickly generate Java code that you can embed in a Java application.

Microsoft SQL Server Version Support

DataDirect XQuery® supports the following versions of Microsoft SQL Server:

• Microsoft SQL Server Enterprise Edition 2005 (64-bit)
• Microsoft SQL Server 2005
• Microsoft SQL Server Enterprise Edition 2000 (64-bit)
• Microsoft SQL Server 2000 (including SP1, SP2, SP3a, and SP4)

Support for Other Relational Databases

DataDirect XQuery® also provides support for the following relational databases:

• Sybase ASE
• IBM DB2
• Oracle

Support for Other Non-XML Data Sources

DataDirect XQuery® also provides support for non-XML sources like these:

• EDI
• Legacy data, like CSV and other flat-file formats