(a) Example retrieval with the XML type (1)

Figure 2-52 shows an example of data retrieval with the XML type. This example retrieves the book information for book ID 126513592 as VARCHAR-type values. The SQL statement for retrieving the data can be specified as follows:

SELECT book_ID, XMLSERIALIZE(book_information AS VARCHAR(32000))
    FROM book_management_table
    WHERE book_ID = 126513592

Figure 2-52 Example of retrieval with the XML type (1)

(b) Example of retrieval with the XML type (2)

This example retrieves results of evaluation by the XQuery expression. Figure 2-53 shows an example of retrieving the titles of books whose category is database. The SQL statement for retrieving the data can be specified as follows:

  SELECT book_ID,
         XMLSERIALIZE(
           XMLQUERY('/book_information/title'
                     PASSING BY VALUE book_information
                     RETURNING SEQUENCE EMPTY ON EMPTY)
           AS VARCHAR(32000))
    FROM book_management_table
    WHERE XMLEXISTS('/book_information[category="database"]'
                     PASSING BY VALUE book_information)

Explanation:

This example uses the XMLQUERY function to retrieve the results of evaluation by the XQuery expression. It uses the XMLEXISTS predicate to eliminate output of rows in which the result of evaluation by the XQuery expression is a null sequence.

Figure 2-53 Example of retrieval with the XML type (2)

(c) Example of retrieval with the XML type (3)

This example outputs values of the XML type as single XML-type values. Figure 2-54 shows an example of joining the titles of books whose category is database and then retrieving the result. The SQL statement for retrieving the data can be specified as follows:

SELECT XMLSERIALIZE(
         XMLAGG(
           XMLQUERY('/book_information/title'
                      PASSING BY VALUE book_information
                      RETURNING SEQUENCE EMPTY ON EMPTY)
         )
         AS VARCHAR(32000))
       FROM book_management_table
       WHERE XMLEXISTS('/book_information[category="database"]'
                       PASSING BY VALUE book_information)

Explanation:

You use the XMLAGG set function to output a value of the XML type on each row as a single XML-type value.

Figure 2-54 Example of retrieval with the XML type (3)

(d) Example of retrieval with the XML type (4)

This example evaluates the XQuery expression for a value of the XML type as a single XML-type value. Figure 2-55 shows an example of retrieving all book information that has the title Comprehensive SQL and with the same category. The SQL statement for retrieving the data can be specified as follows:

SELECT
  XMLSERIALIZE(
    XMLQUERY(
      '$BOOKS/book_information[category=$BOOKS/book_information[title="Comprehensive SQL"]/category]'
      PASSING BY VALUE XMLAGG(book_information) AS BOOKS
      RETURNING SEQUENCE EMPTY ON EMPTY))
    AS VARCHAR(32000))
  FROM book_management_table

Figure 2-55 Example of retrieval with the XML type (4)

(e) Example of retrieval with the XML type (5)

This example uses a substructure index to retrieve data. The following shows an example definition of an index that uses the category element in the book_information as the VARCHAR type key value.

Example definition of substructure index

CREATE INDEX INDX1 ON book_management_table(book_information)
  IN (RDAREA02) KEY FROM '/book_information/category' AS VARCHAR(100)

Use of this index enables the SQL statement shown below to reduce the time required for narrowing down the rows. The following example retrieves book information whose category is network from book_management_table.

Example retrieval using a substructure index

SELECT book_ID,
       XMLSERIALIZE(book_information AS VARCHAR(32000))
  FROM book_management_table
  WHERE XMLEXISTS('/book_information[category="network"]'
                  PASSING BY VALUE book_information)

(f) Example of retrieval with the XML type (6)

This example retrieves data by using an XML-type full-text search index. The following shows an example definition of an XML-type full-text search index for the book_information column.

Example definition of XML-type full-text search index

CREATE INDEX INDX1
  USING TYPE IXXML ON book_management_table(book_information)
  IN (LOBAREA01)

Use of this index enables the SQL statement shown below to reduce the time required for narrowing down the rows. The following example retrieves book information that contains RDBMS in the description from book_management_table.

Example retrieval using an XML-type full-text search index

SELECT book_ID,
       XMLSERIALIZE(book_information AS VARCHAR(32000))
  FROM book_management_table
  WHERE XMLEXISTS('/book_information/description/text()[contains( . ,"RDBMS")]'
                   PASSING BY VALUE book_information)