DB2 10.5 for Linux, UNIX, and Windows

XQuery support

While searching for XML documents in the database, it is also possible to process the search results by using XQuery. By exploiting the DB2® database server's hybrid database engine, an SQL text search query can be combined with XQuery processing.

About this task

This is done by using the db2-fn:sqlquery() input function in the XQuery context. In order to use the XQuery input function, you must switch from SQL to XQuery by using the set language XQuery command, or the query has to be prefixed with the keyword XQuery. This is an important indicator to the parser that it is working with an XQuery expression and must follow the case sensitivity rules and syntax rules that apply to the XQuery language.

The db2-fn:sqlquery() function takes a string literal that represents a full-select. The db2-fn:sqlquery() function returns an XML sequence that represents the concatenation of the XML column values that are selected by the full-select.

The following expression can be used to combine text search and XQuery processing on natively stored XML documents:

XQUERY db2-fn:sqlquery('SELECT c2 FROM t1
           WHERE CONTAINS(c2,
           ''SECTIONS ("/purchaseOrder/item/name") "Rake" '') 
					= 1 ')//shipAddress/name

This query returns all the name elements under the shipAddress element in XML documents that contain a purchasing order item named "Rake". You must explicitly select the XML column (in our case c2) in the SELECT statement.

The previous sample can be extended by a FLWOR construct as follows, and embedded in your application:

XQUERY FOR $item in db2-fn:sqlquery('SELECT c2 FROM t1
           WHERE CONTAINS(c2, '' SECTIONS ("/purchaseOrder/item/name") "Rake" '') 
							= 1 ')
           WHERE $item[@partNo > "800"]        
RETURN $item/price

Note that the full-select of the db2-fn:sqlquery() input function always returns the complete XML document in which a hit occurs.

Consider the following XML document that is stored natively in the database:

<?xml version="1.0" ?>
<dept bldg="101">
		<employee id="901">
					<name>Sabine</name>
					<resume>DB2 programmer</resume>
	 </employee>
		<employee id="902">
					<name>Holger</name>
					<resume>XML expert</resume>
	 </employee>
</dept>

Searching for an employee in your department where the term "XML" is contained in the resume might look as follows:

SELECT c2 FROM t1  WHERE CONTAINS(c2, SECTIONS("/dept/employee/resume") "XML")=1

This select statement returns the complete XML document. Embedding the search query in XQuery as follows:

XQUERY db2-fn:sqlquery('SELECT c2 FROM t1 
         WHERE CONTAINS(c2, 
         ''SECTIONS ("/dept/employee/resume") "XML" '') =1') //employee/name

returns the following two results:

<name>Sabine</name>
<name>Holger</name>

Notice that although the employee Sabine does not have the term "XML" in her resume, she appears in the resulting sequence of this XQuery. This happens because the full-select returns the whole document, that is, it returns the complete XML document that has at least one employee with the term "XML" in the resume.

If you want the query to return only the result <name>Holger</name>, issue the following XQuery statement:

XQUERY for $d in db2-fn:sqlquery('SELECT c2 FROM t1 
         WHERE CONTAINS(c2, 
         ''SECTIONS ("/dept/employee/resume") "XML" '') =1') 
			return §d/dept/employee/name[contains(parent::employee/resume,"XML")];

Net Search Extender filters out all XML documents that have the term XML in the section /dept/employee/resume by using a structure sensitive full-text index on the XML column. Base on the returned subset of XML documents, the return statement return §d/dept/employee/name[contains(parent::employee/resume,"XML")] returns only those <name> elements that have XML in their sibling element called <resume> by navigating the XML document using the XPath axis.