XML search scenario and query examples

This step-by-step scenario shows you how to create a text search index, insert XML documents into a table, and update a text search index. In addition, this information provides six examples of text search queries that use the CONTAINS function.

To create and update a text search index:

  1. Issue the CREATE TABLE statement to create a table XML_DOCUMENTS in schema XMLTEST to store the XML documents: 
    CREATE TABLE XMLTEST.XML_DOCUMENTS (ID INT, XML_DATA XML, PRIMARY KEY (ID));
  2. Run a program that calls the SYSPROC.SYSTS_CREATE stored procedure to create a text search index called XML_INDEX over the XML column:
    call SYSPROC.SYSTS_CREATE('XMLTEST', 'XML_INDEX', 
'XMLTEST.XML_DOCUMENTS(XML_DATA)', '');
  3. Next, insert some XML documents into the table by issuing the INSERT statement:
    INSERT INTO XMLTEST.XML_DOCUMENTS (ID, XML_DATA)
VALUES(1,
      '<BOOK publication_date="2009-01-01">' ||
      ' <TITLE> Text Search Server for DB2 </TITLE>' ||
      ' <ID_NUMBER> 1 ></ID_NUMBER>' ||
      ' <CHAPTER>' ||
      '   <NUMBER> 1 </NUMBER>' ||
      '   <TITLE> Introduction </TITLE>' ||
      '   <ABSTRACT> This chapter will introduce the reader to the capabilities 
                     of Text Search for DB2 for z/OS </ABSTRACT>' ||
      ' </CHAPTER>'||
      ' <CHAPTER>' ||
      '   <NUMBER> 2 </NUMBER>' ||
      '   <TITLE> Creating a Text Search Index </TITLE>' ||
      '   <ABSTRACT> This chapter will explain how to create 
                     a text search index </ABSTRACT>' ||
      ' </CHAPTER>' ||
      '</BOOK>');
    INSERT INTO XMLTEST.XML_DOCUMENTS (ID, XML_DATA)
VALUES(2,
      '<BOOK publication_date="2010-02-01">' ||
      ' <TITLE>  Using the XML data type for DB2 for z/OS </TITLE>' ||
      ' <ID_NUMBER> 2 ></ID_NUMBER>' ||
      ' <CHAPTER>' ||
      '   <NUMBER> 1 </NUMBER>' ||
      '   <TITLE> Introduction </TITLE>' ||
      '   <ABSTRACT> This chapter will introduce the reader to the 
                     DB2 XML data type </ABSTRACT>' ||
      '  </CHAPTER> ' ||
      '  <CHAPTER>' ||
      '    <NUMBER> 2 </NUMBER>' ||
      '    <TITLE>  Inserting XML data into a DB2 table </TITLE>' ||
      '    <ABSTRACT> This chapter will explain how to insert XML 
                      data into a DB2 table </ABSTRACT>' ||
      '  </CHAPTER>' ||
      '  <CHAPTER>' ||
      '    <NUMBER> 3 </NUMBER>' ||
      '    <TITLE>  Searching XML data </TITLE>' ||
      '    <ABSTRACT> This chapter will explain how to query data in XML columns
                      using the CONTAINS and SCORE UDFS </ABSTRACT>' ||
      '  </CHAPTER>' ||						
      '</BOOK>');
    INSERT INTO XMLTEST.XML_DOCUMENTS (ID, XML_DATA)
VALUES(3,
      '<BOOK xmlns="http://www.ibm.com/digital_media_library"' ||
      '      publication_date="2010-02-01">' ||
      ' <TITLE> Using Namespaces with Text Search Server for 
                DB2 for z/OS </TITLE>' ||
      ' <ID_NUMBER> 2 </ID_NUMBER>' ||
      ' <CHAPTER>' ||
      '   <NUMBER> 1 </NUMBER>' ||
      '   <TITLE> Introduction </TITLE>' ||
      '   <ABSTRACT> This chapter will introduce the reader to XML 
                     namespaces </ABSTRACT>' ||
      ' </CHAPTER>' ||
      ' <CHAPTER>' ||
      '   <NUMBER> 2 </NUMBER>' ||
      '   <TITLE> Using default namespaces </TITLE>' ||
      '   <ABSTRACT> This chapter will explain how to use a namespace 
                     in an XML search </ABSTRACT>' ||
      ' </CHAPTER>' ||
      '</BOOK>');
  4. Finally, update the copy of the text search index that is stored on the text search server by calling the SYSPROC.SYSTS_UPDATE stored procedure:
    CALL SYSPROC.SYSTS_UPDATE('XMLTEST', 'XML_INDEX', '');

Example queries

Based on the previous scenario, consider the following examples of how to query the XML documents.

Example 1:

This example finds all documents that have a root element BOOK with a direct descendant TITLE that contains DB2.

SELECT ID 
FROM XMLTEST.XML_DOCUMENTS 
WHERE CONTAINS(XML_DATA, '@xmlxp:''/BOOK/TITLE[. contains("DB2")]'' ') = 1;

Because a namespace prolog is not specified in the search term, no namespace is considered in the search.

Table 1. Result
ID
1
2
3

Example 2:

This example finds all documents that have a root element BOOK with a direct descendant TITLE that contains DB2. Use a default element namespace to indicate that BOOK and TITLE must be in the "http://www.ibm.com/digital_media_library” namespace.

SELECT ID 
FROM XMLTEST.XML_DOCUMENTS 
WHERE CONTAINS(XML_DATA, '@xmlxp:''declare default element namespace
 "http://www.ibm.com/digital_media_library";
 /BOOK/TITLE[. contains("DB2")]'' ') = 1;
Table 2. Result
ID
3

Example 3:

This example finds all documents that have a root element BOOK that has an attribute publication_date after "2010-01-01" and has a child element TITLE that contains DB2. Restrict the search so that tags BOOK and TITLE must not exist in any namespace.

SELECT ID 
FROM XMLTEST.XML_DOCUMENTS 
WHERE CONTAINS(XML_DATA, '@xmlxp:''declare default element namespace ""; 
/BOOK[@publication_date > xs:date("2010-01-01")]/TITLE[. contains("DB2")]'' ') = 1;
Table 3. Result
ID
2

Example 4:

This example finds all documents with a root element BOOK (not in any namespace) that have a direct descendant CHAPTER (also not in a namespace) that contains information about inserting data into an XML table.

SELECT ID 
FROM XMLTEST.XML_DOCUMENTS 
WHERE CONTAINS(XML_DATA, '@xmlxp:''declare default element namespace ""; 
/BOOK/CHAPTER[. contains("inserting XML data into a table")]'' ') = 1;
Note: The text contained within CHAPTER includes the text contained within the ABSTRACT and TITLE elements that are the descendants of CHAPTER. Also, the search string is not case-sensitive, and linguistic variations of the search words are considered.
Table 4. Result
ID
2

Example 5:

This example finds all documents with a root element BOOK (in namespace “http://www.ibm.com/digital_media_library”) that have a direct descendant CHAPTER (also in namespace “http://www.ibm.com/digital_media_library”). CHAPTER must have a direct descendant NUMBER (in namespace “http://www.ibm.com/digital_media_library") with a value of 1, and also contain text information about searching an XML namespace.

SELECT ID 
FROM XMLTEST.XML_DOCUMENTS 
WHERE CONTAINS(XML_DATA, '@xmlxp:''declare namespace ns1 = 
"http://www.ibm.com/digital_media_library"; 
/ns1:BOOK/ns1:CHAPTER[. contains("search XML using a namespace") 
and NUMBER = 1]'' ') = 1;

Document 3 is the only document with tags in the correct namespace, but it has key word matches only in a chapter with a number value of 2 (not 1).

No rows are returned.

Table 5. Result
ID
 

Example 6:

This example finds all documents with a root element BOOK (in namespace “http://www.ibm.com/digital_media_library”) that have a direct descendant CHAPTER (in namespace “http://www.ibm.com/digital_media_library”). CHAPTER must have a direct descendant NUMBER (in namespace “http://www.ibm.com/digital_media_library”) with a value of 1. BOOK must have a descendant CHAPTER (not necessarily with a NUMBER descendant) that contains text information about searching an XML namespace.

SELECT ID 
FROM XMLTEST.XML_DOCUMENTS 
WHERE CONTAINS(XML_DATA, '@xmlxp:''declare namespace ns1 = 
"http://www.ibm.com/digital_media_library"; 
/ns1:BOOK[ns1:CHAPTER contains("search XML using a namespace")]
/ns1:CHAPTER[ns1:NUMBER = 1]'' ') = 1;

Document 3 does have a CHAPTER element that matches the CONTAINS criteria, and also has a CHAPTER element with a descendant NUMBER that has a value of 1. Therefore, document 3 is a match for this query.

Table 6. Result
ID
3