Nonstop Database, HiRDB Version 9 UAP Development Guide
This section describes examples that use HiRDB XML Extension. For details about the abstract data type functions provided by HiRDB XML Extension, see the manual HiRDB Version 9 SQL Reference.
This section explains examples that use XML documents to manage book information.
The examples presented below are based on a table defined for the discussion of database creation in the HiRDB Version 9 Installation and Design Guide (table containing abstract data types provided by plug-ins).
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)
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)
Figure 2-53 Example of retrieval with the XML type (2)
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)
Figure 2-54 Example of retrieval with the XML type (3)
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)
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.
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.
SELECT book_ID, XMLSERIALIZE(book_information AS VARCHAR(32000)) FROM book_management_table WHERE XMLEXISTS('/book_information[category="network"]' PASSING BY VALUE book_information)
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.
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.
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)
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.