xmlcolumn-contains function

Syntax

>>-db2-fn:xmlcolumn-contains(string-literal,search-argument-+-----------------------------+-)-><
                                                            |                         (1) |     
                                                            '-,options-string-literal-----'     

1. options-string-literal must conform to the rules for search-argument-options.

search-argument-options

   .------------------------------.   
   V  (1)                         |   
|--------+----------------------+-+-----------------------------|
         +-QUERYLANGUAGE=locale-+     
         +-RESULTLIMIT=value----+     
         |          .-OFF-.     |     
         '-SYNONYM=-+-ON--+-----'     

1. You can specify each option only once.

string-literal

Specifies the name of a XML data type column to be searched by db2-fn:xmlcolumn-contains. The value of string-literal is case sensitive and must match the case of the table and column name. You must qualify the column name using a table name or a view name. The SQL schema name is optional. If you do not specify the SQL schema name, the value of CURRENT SCHEMA is used.

The column must have a text search index.

search-argument

An expression that returns an atomic string value or an empty sequence. The string cannot be all space characters or an empty string. The string must be castable to the type VARCHAR according to the rules of XMLCAST with a maximum length of 4096 bytes.

options-string-literal

Specifies the search argument options that are in effect for the function.

The options that you can specify as part of the search-argument-options are as follows:

QUERYLANGUAGE = locale

Specifies the locale that the DB2 Text Search engine uses when performing a text search on a DB2 text column. The value can be any of the supported locales. If you do not specify QUERYLANGUAGE, the default is the locale of the text search index. If the LANGUAGE parameter of the text search index is AUTO, the default value for QUERYLANGUAGE is en_US.

RESULTLIMIT = value

If the optimizer chooses a plan that calls the search engine for each row of the result set to obtain the SCORE, then the RESULTLIMIT option has no effect on performance. However, if the search engine is called once for the entire result set, RESULTLIMIT acts like a FETCH FIRST clause.

When using multiple text searches that specify RESULTLIMIT in the same query, use the same search-argument. If you use different search-argument values, you might not receive the results that you expect.

For partitioned text indexes, the result limit is applied to each partition separately.

For an example of what might happen when using multiple text searches and a solution, see the last example in Examples.

SYNONYM = OFF | ON

Specifies whether to use a synonym dictionary that is associated with the text search index. The default is OFF. To use synonyms, add the synonym dictionary to the text search index using the Synonym Tool.

OFF

Do not use a synonym dictionary.

ON

Use the synonym dictionary associated with the text search index.

Returned values

The returned value is a sequence that is the concatenation of the non-null XML values from the column that is specified by string-literal. The non-null XML values are returned in a nondeterministic order. The XML values are the XML documents where the SQL CONTAINS function using search-argument for the column specified by string-literal would return 1. If there are no such XML values, an empty sequence is returned.

If search-argument is an empty sequence, an empty sequence is returned. If search-argument is an empty string or string containing all space characters, an error is returned. If the third argument is null, the result is as if you did not specify the third argument.

If the column that you specify using string-literal does not have a text search index, an error is returned.

The db2-fn:xmlcolumn-contains function is related to the db2-fn:sqlquery function, and both functions can produce the same result. However, the arguments of the two functions differ in case sensitivity. The first argument, string-literal, in the db2-fn:xmlcolumn-contains function is processed by XQuery and is case sensitive. Because table names and column names in a DB2 database are uppercase by default, the first argument of db2-fn:xmlcolumn-contains is usually uppercase. The first argument of the db2-fn:sqlquery function is processed by SQL, which automatically converts identifiers to uppercase.

The following function calls are equivalent and return the same results assuming that the PRODUCT table is in the schema currently assigned to CURRENT SCHEMA:

	db2-fn:xmlcolumn-contains("PRODUCT.DESCRIPTION", "snow shovel")

	db2-fn:sqlquery("select description from product
  where contains(description, 'snow shovel')) = 1")

Examples

The following examples use the DB2 Text Search engine to perform searches. The columns being searched are XML columns and have a text search index.

The first function searches for XML documents stored in the PRODUCT.DESCRIPTION column that contain the words snow and shovel. The function sets the maximum number of returned documents to two. If the text search returns a large number of documents, you can optimize the search by using the RESULTLIMIT option to limit the maximum number of documents returned.

	db2-fn:xmlcolumn-contains('PRODUCT.DESCRIPTION', 'snow shovel', 'RESULTLIMIT=2')

The function returns the XML documents that match the search criteria. The documents might contain more than just a product description. For example, the following XML fragment consists of two product descriptions from an XML column. Each document contains a product description and information such as the product name, price, weight, and product ID.

<product xmlns="http://posample.org" pid="100-100-01">
	<description>
    <name>Snow Shovel, Basic 22 inch</name>
    <details>Basic Snow Shovel, 22 inches wide, straight handle with 
       D-Grip</details>
    <price>9.99</price>
    <weight>1 kg</weight>
  </description>
</product>
<product xmlns="http://posample.org" pid="100-101-01">
  <description>
    <name>Snow Shovel, Deluxe 24 inch</name>
    <details>A Deluxe Snow Shovel, 24 inches wide, ergonomic curved handle
       with D-Grip</details>
    <price>19.99</price>
    <weight>2 kg</weight>
  </description>
</product>

The following function searches the XML column STUDENT_ESSAYS.ABSTRACTS for 10 student essays that contain the phrase fossil fuel in Spanish, which is combustible fósil. The function specifies es_ES (Spanish as spoken in Spain) as the language to use for the text search and uses the synonym dictionary that was created for the associated text search index. The function optimizes the search by using RESULTLIMIT to limit the number of results.

	db2-fn:xmlcolumn-contains('STUDENT_ESSAYS.ABSTRACTS', '"combustible fosil"',
  	'QUERYLANGUAGE=es_ES RESULTLIMIT=10 SYNONYM=ON')

The following example uses db2-fn:xmlcolumn-contains to find XML documents stored in the PRODUCT.DESCRIPTION column that contain the word ergonomic. The expression returns the name of the product whose price is less than 20.

	xquery
	declare default element namespace "http://posample.org";
	db2-fn:xmlcolumn-contains(
 	 'PRODUCT.DESCRIPTION', 'ergonomic')/product/description[price < 20]/name

The previous expression returns only the name elements from the returned XML documents. For example, if the term ergonomic is in the product description of the product Snow Shovel, Deluxe 24 inch, the expression returns a name element similar to the following one:

	<name xmlns="http://posample.org" >Snow Shovel, Deluxe 24 inch<name>

The following expression uses db2-fn:xmlcolumn-contains to find the XML documents from the PRODUCT.DESCRIPTION column that contain the words ice and scraper. The expression uses the product IDs from the product descriptions to find purchase orders in the PURCHASEORDER table that contain the product IDs. The expression returns the customer IDs from purchase orders that contain the product IDs from the matched XML description documents.

	xquery
	declare default element namespace "http://posample.org";
	for $po in db2-fn:sqlquery('
	  select XMLElement(Name "po", XMLElement(Name "custid", purchaseorder.custid),
	    XMLElement(Name "porder", purchaseorder.porder))
	  from purchaseorder')
	let $product := db2-fn:xmlcolumn-contains('PRODUCT.DESCRIPTION',
	  'ice scraper')/product
	where $product/@pid = $po/porder/PurchaseOrder/item/partid
	order by $po/custid 
	return $po/custid

The expression returns custid elements containing the customer IDs. The elements are in ascending order. For example, if three purchase orders matched and the purchase orders had customer IDs 1001, 1002, and 1003, the expression returns the following elements:

	<custid xmlns="http://posample.org">1001</custid>
	<custid xmlns="http://posample.org">1002</custid>
	<custid xmlns="http://posample.org">1003</custid>

If there are multiple text searches in the same query, the DB2 Text Search engine combines the multiple text search results and returns them. For example, the following SELECT statement searches for employee resumes that contain the exact phrases ruby on rails and ajax web. The WHERE clause contains two text searches. Each text search returns a maximum of 10 results, and each text search uses a different search argument to search for employee resumes. The statement might return fewer than 10 employee IDs even if there are more than 10 employee resumes that contain both phrases.

	SELECT EMPNO FROM EMP_RESUME
	WHERE XMLEXISTS('db2-fn:xmlcolumn-contains(''EMP_RESUME.XML_FORMAT'',
	   ''"ruby on rails"'', ''RESULTLIMIT=10'')')
	AND XMLEXISTS('db2-fn:xmlcolumn-contains(''EMP_RESUME.XML_FORMAT'',
	   ''"ajax web"'', ''RESULTLIMIT=10'')')

For the previous statement, DB2 Text Search returns at most 10 rows for each text search. However, if the resumes in the returned rows contain only one of the phrases (not both phrases), no employee IDs are returned.

One way to modify the SELECT statement is to combine the two text searches in the WHERE clause into a single text search. The following statement uses a single text search and returns employee IDs whose resumes have both the phrase ruby on rails and ajax web:

	SELECT EMPNO FROM EMP_RESUME 
	WHERE XMLEXISTS('db2-fn:xmlcolumn-contains(''EMP_RESUME.XML_FORMAT'',
	    ''"ruby on rails" AND "ajax web"'', ''RESULTLIMIT=10'')')

xquery for $i in db2-fn:xmlcolumn-contains('DBCP1208.T_AUTO.T_XML',
'@xpath:''//en//en[. contains("purpose") and @a1 = "value for en\:attribute1"
and @slope = "9"] '' ') return $i/*/fn:string