Query examples

To begin writing queries, you must understand the query language concepts, syntax, and grammar. These sample queries cover item types, text searches, arithmetic operations in conditions, traversal of links and references, checked out items, and querying by timestamp.

Here are some hints to help you understand the query examples:

Follow the query string as you would follow a directory structure.
/ (single forward slash) indicates a direct child relationship
// (double forward slash) indicates either a child relationship or a descendant relationship
. (dot) represents the current component in the hierarchy
.. (dot dot) represents the parent of the current component
@ (AT sign) denotes an attribute
[ ] (square brackets) denote a conditional statement or a list
=> (DEREFERENCE operator) represents linking or referencing action
The result of the query must be a component (for example, an attribute cannot be the last thing in the path)

For more examples and documentation, see the SSearchICM sample.

Important: When specifying attributes as query conditions, use the non-translated 15 character attribute name. Do not use the display name for your query conditions.

For more information about attribute names and display names, see the SAttributeDefinitionCreationICM and SSearchICM API education samples.

The sample queries provided in this section are based on the sample data model, Figure 1, and the sample XML document.

Using the IN operator

The IN operator is used with literals (integer, float, string), and should be used to match an attribute with a set of possible values. Using IN instead of multiple equality or inequality predicates connected through OR operators result in shorter queries. The IN operator should be used if existing queries cause a DK_ICM_MSG_QL_TOO_LONG_OR_TOO_COMPLEX exception.

To search for a journal that has 4, 8, or 12 pages, the search string without the IN operator:

/Journal[(@NumPages = 4) OR (@NumPages = 8) 
OR (@NumPages = 12)]

To search for a journal that has 4, 8, or 12 pages, the search string with IN operator is:

/Journal[@NumPages IN (4,8,12)]

To search for books written by authors with surnames other than Smith or Jones, the search string without the IN operator:

/Book[Book_Author[(@LastName != "Smith") 
AND (@LastName != "Jones")]]

To search for books written by authors with surnames other than Smith or Jones, the search string with the IN operator:

/Book[Book_Author[@LastName NOT IN ("Smith", "Jones")]]

Note: In IBM® Content Manager V8.6 and later, searching for data and sorting data can be configured to be case-insensitive. In earlier versions, they were always case-sensitive. This means that queries used in earlier versions might give different results in version V8.6 and later. (This does not affect text search, which has always been case-insensitive.) For example, if the LastName attribute is configured for case-insensitive search, the following string lists authors whose surname is "Smith", "SMITH", "smith", and so on:

(@LastName = "Smith")

If the LastName attribute is configured for case-sensitive search, this string lists only authors whose name exactly matches "Smith"; that is, starting with an uppercase S, and the rest in lowercase.

Access to components

The following query finds all journals.

/Journal

The / starts at the implicit root of the XML document, which in this case is the entire library server. Each item type is an element under this root. If LS.xml is the XML document that contains the entire model as described earlier, then the explicit document root is document (LS.xml).

Access to attributes

The following query finds all journal articles that have 50 pages.

/Journal[@NumPages=50]

The predicate

@NumPages =
        50

evaluates to true for all journals that have the attribute NumPages set to 50.

Multiple item types

The following queries finds all books or journals that have Williams as one of the authors, and a section title beginning with XML.

(/Book | /Journal)
[(.//Journal_Author/@LastName = "Williams"
OR .//Book_Author/@LastName = "Williams")
AND (.//Book_Section/@Title LIKE "XML%"
OR .//Journal_Section/@Title LIKE "XML%")]

(/Book[.//Book_Author/@LastName = "Williams"
AND .//Book_Section/@Title LIKE "XML%"])
| (/Journal[.//Journal_Author/@LastName = "Williams"
 AND .//Journal_Section/@Title LIKE "XML%"])

.//Journal_Author means that a component Journal_Author should be found either directly under the current component in the path (which in the first case is either a Book or a Journal) or somewhere deeper in the hierarchy. The LIKE operator is used in conjunction with a wildcard character, in this case %.

BETWEEN operator: Arithmetic operations in conditions

The following query finds all journals with the number of pages between 45 and 200.

/Journal[@NumPages BETWEEN 49-4 AND 2*100]

You can perform arithmetic operations to calculate the resulting values to be used with the BETWEEN operator.

If you are looking for all journals with the number of pages between 10 and 200, you can use the BETWEEN operator available in the query language.

"/Journal [@NumPages BETWEEN 10 AND 200]"

The values that are used with the BETWEEN operator are included in the range, meaning that the lower bound includes the value of 10 and the upper bound includes the value of 200.

The BETWEEN operator should make your query easier to read, especially if multiple query conditions are used in the same query string. The BETWEEN operator can be used with attributes of numeric types, and also with attributes of other types (for example, date, string).

Traversal of links in the forward direction

This query finds all articles in journals edited by Williams that are contained in SIGs with title SIGMOD.

/SIG[@Title = "SIGMOD"]/OUTBOUNDLINK
 [@LINKTYPE = "contains"]/@TARGETITEMREF =>
 Journal[Journal_Editor/@LastName = "Williams"]
 /Journal_Article

This is an example of following links in the forward direction. The virtual XML component OUTBOUNDLINK and its attribute TARGETITEMREF are used to traverse to all Journals and then finally the underlying Journal_Articles. The last component in the path is what is returned as the result of the query. The result can be constrained by traversing only specific link types (contains in this example) to a specific type of items (Journal in this example). Since the conceptual XML representation of the library server looks at inbound and outbound links as being parts of items, the dereferencing operator can be used to relieve applications from writing explicit joins.

Traversal of links in the backward direction

The following query finds all items of any type that have journals which cost less than five dollars with articles by author Nelson.

/Journal[@Cost < 5
AND .//Journal_Author/@LastName = "Nelson"]
/INBOUNDLINK[@LINKTYPE = "contains"]
/@SOURCEITEMREF => *

This is an example of following links in the backward direction. The wildcard *, following the dereference operator => ensures that items of ANY type are returned as the result.

Basic text search (contains-text-basic and score-basic functions)

In general, use basic text search in your applications whenever possible because the syntax works for all databases and search engines. Only use a specific search engine syntax if you need to make use of specific search engine features.

This query finds all journals that contain the text Java in the title, and sorts the results by title:

/Journal [contains-text-basic (@Title, " 'Java' ")=1] SORTBY  (@Title)

This query finds all journal articles that contain the text Java and the text JDK 1.6 but not the text XML by using the simplified (basic) text search syntax and sort the results by the text search score:

//Journal_Article
[contains-text-basic(@Title, " +Java -XML +'JDK 1.6'")=1]
SORTBY (score-basic(@Title, " +Java -XML +''JDK 1.6' "))

This is an example of performing text search by using the simplified text search syntax. Use a + to indicate the words or phrases that should be present in the attribute Title, and, similarly, use a - to exclude other words or phrases. The score-basic function works similarly to the score function in the previous example, but uses a simplified syntax.

The score-basic and the score functions support the SORTBY clause. However, the SORTBY clause can be used in many functions provided that they return a scalar result that can be used to sort the result. For example, the following query is valid for returning all versions of all journal items in the system sorted by length of the title attribute value:

/Journal SORTBY( LENGTH(@title) )

Advanced text search (contains-text and score functions)

This query finds journal articles with author Richardt that contain the text Java and the text XML. The results are ordered by the text search score. The following examples perform text searches with the contains-text function. For the syntax supported by this function, see the Db2 documentation.

Important: The contains-text-db2 function should be equated with 1 to be true and 0 to be false. The score function uses the ranking information returned by Db2® Net Search Extender, which is used in this case to sort the resulting journal articles through SORTBY.

Text search with Db2 Net Search Extender (NSE)

//Journal_Article[Journal_Author/@LastName = "Richardt"
AND contains-text-db2(@Text, " 'Java' & 'XML' ")=1]
SORTBY(score(@Text, " 'Java' & 'XML' "))

Text search for Oracle

//Journal_Article[Journal_Author/@LastName = "Richardt" 
AND contains-text-oracle(@Text, " 'Java' & 'XML' ", 1)>0]
SORTBY(score(1))

Text search with Db2 Text Search (DB2TS)

//Journal_Article[contains-text-db2ts(@Title, " 'Design' OR 'System'  ")=1]

In Db2 Text Search, you can also append additional search argument options to the query:

QUERYLANGUAGE: Specifies the locale of the query. The default is en_US.
RESULTLIMIT: Specifies the maximum number of results to return from the underlying search engine. The value can be an integer value between 1 and 2 147 483 647. If the RESULTLIMIT option is not specified, then the query does not limit the results.
SYNONYM: Specifies whether to use a synonym dictionary that is associated with the text search index. You can add a synonym dictionary to a collection by using the synonym tool. The default is OFF.

Example of a query that uses these options (finds ten students at random who wrote online essays that contain the words fossil fuel in Spanish):

//STUDENT_ESSAYS[contains-text-db2ts(@TERM_PAPER, "combustible fosil",
  "QUERYLANGUAGE=es_ES RESULTLIMIT=10 SYNONYM=ON")=1]

For more information on Db2 Text Search, see Db2 Text Search.

Text search (contains-text and attribute sorting)

This query finds all journals that have either the word Design or the word Index in their title and sorts the results in descending order by their title. This is another example of performing text search by using the contains-text function. The sorting in this case uses the DESCENDING operator on the Title attribute. The default for the SORTBY is ASCENDING.

Text search with Db2 Net Search Extender (NSE)

/Journal 
[Journal_Article[contains-text-db2(@Title, " 'Design' | 
  'Index'  ")=1]]
SORTBY (@Title DESCENDING)

Text search for Oracle

/Journal 
[Journal_Article[contains-text-oracle(@Title, " 'Design' | 
  'Index'  ")>0]]
SORTBY (@Title DESCENDING)

Text search with Db2 Text Search (DB2TS)

/Journal[Journal_Article[contains-text-db2ts(@Title, " 'Design' OR 
  'System'  ")=1]] SORTBY (@Title)

Text search on resource items

This query finds text resources in a text resource item type TextResource that contain the text Java and the text XML. This is an example of performing text search inside of the resources in the resource manager. The TIEREF attribute is used as a representation of the resource that is represented by the item of type TextResource. For the syntax supported by contains-text-db2 function, see the IBM Db2 Net Search Extender Administration and User's Guide in the Db2 documentation.

Text search with Db2 Net Search Extender (NSE)

/TextResource[contains-text-db2(@TIEREF, " 'Java' & 'XML' ")=1]

Text search for Oracle

/TextResource[contains-text-oracle(@TIEREF, " 'Java' & 'XML' ")>0]

Text search with Db2 Text Search (DB2TS)

/TextResource[contains-text-db2ts(@TIEREF, " 'TConnectICM' ")=1]

Traversal of references in the forward direction

This query finds all the frequently asked questions for conferences, for which the conference notes refer to books with titles mentioning IBM Content Manager..

 /Conference/Conference_Note [@PublicationRef => 
Book[@Title LIKE "%CM%"]]
/Conference_FAQ

Traversal of references in the forward direction

This query finds all chapters of books referenced in the notes of conferences related to Internet.

/Conference[@Title LIKE "%Internet%"]
/Conference_Note/@PublicationRef =>
*/Book_Chapter

Traversal of references in the reverse direction

This query finds all the components that have references pointing to any books.

/Book/REFERENCEDBY/@REFERENCER => *

Traversal of references in the reverse direction

This query finds all the frequently asked questions under conference notes that refer to books about XML.

/Book[@Title LIKE "XML"]/REFERENCEDBY/@REFERENCER =>
Conference_Note/Conference_FAQ

Since the reference attributes originate inside of the Conference_Note component, this is the component that must appear as the first component after the dereference operator. This query produces an empty result set if, for example, Conference follows the => operator.

Traversal of references in the reverse direction

This query finds all the components that contain XML in their remarks and that have references pointing to books.

/Book/REFERENCEDBY/@REFERENCER => 
*[@Remark LIKE "%XML%"]

Latest version function

This query finds all the journals of the latest version. By default, all versions of the indicated component type view that match the query are returned. VERSIONID is a system-defined attribute that is contained in every component type.

/Journal[@VERSIONID = latest-version(.)]

Latest version function on the target of the dereference

This query finds all the books of the latest version that are referenced in the notes of any conferences.

/Conference/Conference_Note/@SYSREFERENCEATTRS =>
Book[@VERSIONID = latest-version(.)]

Latest version function on wildcard components

This query finds all the components of the latest version that have references pointing to any books.

/Book/REFERENCEDBY/@REFERENCER => *
[@VERSIONID = latest-version(.)]

System-defined attributes

This query finds all the root components with a specific item ID.

/*[@ITEMID = "A1001001A01J09B00241C95000"]

Text search on document model

This query finds all documents that contain the word XML in any one of its parts. The query language offers a virtual component ICMPARTS that allows access to all the ICM Parts item types contained under a specific item type of Document classification.

Text search with Db2 Net Search Extender (NSE)

/Doc[contains-text-db2(.//ICMPARTS/@TIEREF, " 'XML' ")=1]

Text search for Oracle

/Doc[contains-text-oracle(.//ICMPARTS/@TIEREF, " 'XML' ")>0]

Text search with Db2 Text Search (DB2TS)

/Doc[contains-text-db2ts(.//ICMPARTS/@TIEREF, " 'XML' ")=1]

Document model (access to ICM Parts)

This query finds all the parts of the document with the storage ID of 555.

/Doc[@ArchiveID = 555]/ICMPARTS/
@SYSREFERENCEATTRS => *

Document model (access to ICM Parts)

This query finds all the parts in all of the documents in the system.

 //ICMPARTS/@SYSREFERENCEATTRS => *

Because both the Doc and Paper item types have been defined as being Documents in the system, the ICM Parts from both of them are returned in the result.

Existence of attributes

This query finds all root components that have a title.

  /*[@Title]

To eliminate the restriction that only root components should be returned, the query can be rewritten to start with a double-slash.

//*[@Title]

List of both literals and expressions

This query finds all journals that have a title that is equal to either its article's title, its section's title, or

IBM
        Systems Journal

/Journal[@Title = [Journal_Article/@Title,
.//Journal_Section/@Title,"IBM Systems Journal"]]

List of literals

This query finds all books that cost either $10, $20, or $30.

/Book[@Cost IN (10, 20, 30)]

Although it is possible to perform the same query by using the list operator, as in /Book[@Cost = [10, 20, 30]] (sub-optimal), for a large number of literals this approach might lead to errors because the generated SQL would be too long or too complex. If all the elements in the list are literals, always use the IN operator for the best performance and the shortest SQL. You can use the IN operator for literals of any type, including non-numeric types.

List of a result of query

This query finds all journals or all books with the title Star Wars.

 [/Journal, /Book[@Title = "Star Wars"]]

Attribute groups

This query finds all details on documents in which the description is at least 20 pages long.

/Doc[Doc_Description/@PageSummary.NumPages >=
20]//Doc_Details

If an attribute (for example, NumPages) is contained in an attribute group (for example, PageSummary), then you must refer to that attribute as GroupName.AttrName (for example, PageSummary.NumPages). The attribute @NumPages would not be found under Doc_Description.

Checked out items

This query finds all items of the Journal item type that are currently checked out.

/Journal [ICMCHECKEDOUT]

The ICMCHECKEDOUT XML element is a sub-element of only the root components, but not of the descendant components. Therefore, if the ICMCHECKEDOUT element is written in a query as a condition of a child component (for example,

//Journal_Author
[ICMCHECKEDOUT]

), then no results return.

Whenever an item is checked out, all versions of that item are checked out. Therefore, when an ICMCHECKEDOUT element is applied to a checked out item, all currently available versions will be returned. To retrieve a specific version, you can still use the @VERSIONID query syntax (for example, /Journal [ICMCHECKEDOUT AND @VERSIONID = 4]). For the latest version, you can use the latest-version() function.

Checked out items by person

This query finds all items checked out by SMITH.

/Journal [ICMCHECKEDOUT/@ICMCHKOUTUSER = "SMITH"]

The value for ICMCHKOUTUSER must be entered in uppercase in a query. Since the content servers store user IDs as uppercase, all queries must query for user IDs by using uppercase. All attribute data pertaining to user IDs must store them in uppercase as well.

Checked out items by timestamp

This query finds all items checked out after 2023-08-02-17.29.23.977001.

/Journal [ICMCHECKEDOUT/@ICMCHKOUTTS > "2023-08-02-17.29.23.977001"]

Text search by XML

This query searches XML documents with Db2 Text Search (DB2TS):

/Mimetype_XML[contains-text-db2ts(.//ICMPARTS/@TIEREF, " @xmlxp:''//Extension'' ")=1]

Sort with mixed results

The following query sorts by three attributes, and does not require each attribute to exist on all of the item types. If the attribute does not exist, NULL is used for sorting.

(/Journal | /Book | /Conference ) SORTBY (@Title, @Organization, @PublishDate)

Restriction: Wild card (*) queries with no scope defined, for example

/* SORTBY
          (@Title)

, will fail unless you use a common attribute ("LASTCHANGEDUSERID") to define the scope:

/*[@LASTCHANGEDUSERID] SORTBY (@Title)

You can also use the sorting attribute itself to define the scope. The following query sorts only the item types that have the attribute Title:

/*[@Title] SORTBY (@Title)

Query on-hold items or all holds for a held item

When dealing with item holds, you must specify LINKTYPE=DKHold in the query string.

This query returns the on-hold items in a hold container:

/hold/OUTBOUNDLINK[@LINKTYPE ="DKHold"]/@TARGETITEMREF => * [@Title LIKE "Java%"]

This query gets all the holds for a held item:

/onhold/INBOUNDLINK[@LINKTYPE= "DKHold"]/@SOURCEITEMREF => *

If you need to query both Link and Hold related tables/items without specifying LINKTYPE, set the query option DK_CM_PARM_USE_LINK_HOLD_UNION to true to force the query to access union view (which contains both hold and normal LINKTYPEs).

If a query string specifies more than one LINKTYPEs and one of them is DKHold, for example, @LINKTYPE IN ("DKHold", "DkFolder"), a union view table is used (which might have a performance impact).