Altova XMLSpy 2024 Enterprise Edition

An XQuery document can be used to query an XML database (XML DB). Currently this XQuery functionality is supported only for IBM DB2 databases. The mechanism for querying an XML DB using XQuery essentially involves: (i) indicating to the XQuery engine that XML in a DB is to be queried—as opposed to XML in an XML document; and (ii) accessing the XML data in the DB.

The steps for implementing this mechanism are as follows and are described in detail below:

1.Set up the XQuery document to query the XML DB by inserting the XQUERY keyword at the start of the document.

2.For the active XQuery document, enable DB support (via the Info window) and connect to the DB (using the Quick Connect dialog).

3.In the XQuery document, insert DB-specific XQuery extensions so as to access the DB data and make it available for XQuery operations.

4.Execute the XQuery document in XMLSpy.

Setting up the XQuery document to query the XML DB

To set up the XQuery document to query an XML DB, open the XQuery document (or create a new XQuery document) and enter the keyword XQUERY (casing is irrelevant) at the start of the document (before the prolog); see examples below.

XQUERY (: Retrieve details of all customers :)

declare default element namespace "https://www.altova.com/xquery/databases/db2";

<a> {db2-fn:xmlcolumn("CUSTOMER.INFO")} </a>

If the document uses the optional xquery version expression, the XQUERY keyword is still required:

XQUERY xquery version "1.0"; (: Retrieve details of all customers :)

declare default element namespace "http://https://www.altova.com/xquery/databases/db2";

<a> {db2-fn:xmlcolumn("CUSTOMER.INFO")} </a>

Enable DB support for XQuery and connect to the DB

DB support for an XQuery document is enabled by checking the Enable Database Support check box in the Info window (screenshot below). Note that DB Support must be enabled for each XQuery document separately and each time an XQuery document is opened afresh.

When you enable DB support in the Info window, a Quick Connect dialog pops up, which enables you to connect to a database. Currently, only IBM DB2 databases are supported. How to connect to a DB is described in the section, Connecting to a Database. If connections to data sources already exist, then these are listed in the Data Sources combo box of the Info window (screenshot below), and one of these data sources can be selected as the data source for the active XQuery document. In the Info window, you can also select the root object from among those available in the Root Object combo box.

The Quick Connect dialog (which enables you to connect to a DB) can be accessed at any time by clicking the icon in the Info window.

IBM DB2-specific XQuery language extensions

Two IBM DB2-specific functions can be used in XQuery documents to retrieve data from an IBM DB2 database:

•db2-fn:xmlcolumn retrieves an entire XML column without searching or filtering the column.

•db2-fn:sqlquery retrieves values based on an SQL SELECT statement

The XML data retrieved using these functions can then be operated on using standard XQuery constructs. See examples below.

db2-fn:xmlcolumn: The argument of the function is a case-sensitive string literal that identifies an XML column in a table. The string literal argument must be a qualified column name of type XML. The function returns all the XML data in the column as a sequence, without applying a search condition to it. In the following example, all the data of the INFO (XML) column of the CUSTOMER table is returned within a top-level <newdocelement> element:

XQUERY (: Retrieve details of all customers :)

declare default element namespace "https://www.altova.com/xquery/databases/db2";

<newdocelement> {db2-fn:xmlcolumn("CUSTOMER.INFO")} </newdocelement>

The retrieved data can then be queried with XQuery constructs. In the example below, the XML data retrieved from the INFO (XML) column of the CUSTOMER table is filtered using an XQuery construct so that only the profiles of customers from Toronto are retrieved.

XQUERY (: Retrieve details of Toronto customers :)

declare default element namespace "https://www.altova.com/xquery/databases/db2";

<newdocelement> {db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo[addr/city='Toronto']} </newdocelement>

db2-fn:sqlquery: The function takes an SQL Select statement as its argument and returns a sequence of XML values. The retrieved sequence  is then queried with XQuery constructs. In the following example, the INFO column is filtered for records in the CUSTOMER table that have a CID field  with a value between 1000 and 1004. Note that while SQL is not case-sensitive, XQuery is.

XQUERY (: Retrieve details of customers by Cid:)

declare default element namespace "https://www.altova.com/xquery/databases/db2";

<persons>

  {db2-fn:sqlquery("SELECT info FROM customer WHERE CID>1000 AND CID<1004")/

  <person>

     <id>{data(@Cid)}</id>

     <name>{data(name)}</name>

  </person>}

</persons>

The XQuery document above returns the following output:

<persons xmlns="https://www.altova.com/xquery/databases/db2">

  <person>

 <id>1001</id>

 <name>Kathy Smith</name>

  </person>

  <person>

 <id>1002</id>

 <name>Jim Jones</name>

  </person>

  <person>

 <id>1003</id>

 <name>Robert Shoemaker</name>

  </person>

</persons>

Note the following points:

•The default element namespace declaration in the prolog applies for the entire XQuery document and is used for navigation of the XML document as well as for construction of new elements. This means that the XQuery selector name is expanded to <default-element-namespace>:name, and that constructed elements, such as persons, are in the default element namespace.

•The SQL Select statement is not case-sensitive.

•The WHERE clause of the Select statement should reference another database item—not a node inside the XML file being accessed.

•The "/" after the db2-fn:sqlquery function represents the first item of the returned sequence, and this item is the context node for further navigation.

Execute the XQuery

To execute the XQuery document, select the XQuery Execution command (XSL/XQuery menu). Alternatively, press Alt+F10 or click the XQuery Execution icon . The result of the execution is displayed in a new document.