Using the XML Extender search features, you can search XML data in a column based on the document structure, that is on elements and attributes. To search the column data you use a SELECT statement in several ways and return a result set based on the matches to the document elements and attributes. You can search column data using the following methods:
These methods are described in the following sections and use examples with the following scenario. The application table SALES_TAB has an XML column named ORDER. This column has three side tables, ORDER_SIDE_TAB, PART_SIDE_TAB, and SHIP_SIDE_TAB. A default view, sales_order_view, was specified when the ORDER column was enabled and joins these tables using the following CREATE VIEW statement:
CREATE VIEW sales_order_view(invoice_num, sales_person, order, order_key, customer, part_key, price, date) AS SELECT sales_tab.invoice_num, sales_tab.sales_person, sales_tab.order, order_side_tab.order_key, order_side_tab.customer, part_side_tab.part_key, ship_side_tab.date FROM sales_tab, order_side_tab, part_side_tab, ship_side_tab WHERE sales_tab.invoice_num = order_side_tab.invoice_num AND sales_tab.invoice_num = part_side_tab.invoice_num AND sales_tab.invoice_num = ship_side_tab.invoice_num
Direct query with subquery search provides the best performance for structural search when the side tables are indexed. You can use a query or subquery to search side tables correctly.
Example: The following statement uses a query and subquery to directly search a side table:
SELECT sales_person from sales_tab WHERE invoice_num in (SELECT invoice_num from part_side_tab WHERE price > 2500.00)
In this example, invoice_num is the primary key in the SALES_TAB table.
You can have the XML Extender create a default view that joins the application table and the side tables using a unique ID. You can use this default view, or any view which joins application table and side tables, to search column data and query the side tables. This method provides a single virtual view of the application table and its side tables. However, the more side tables that are created, the more expensive the query.
Tip: You can use the root_id, or DXXROOT_ID (created by the XML Extender), to join the tables when creating your own view.
Example: The following statement searches a view
SELECT sales_person from sales_order_view WHERE price > 2500.00
The SQL statement returns the values of sales_person from the joined view sales_order_view table which have line item orders with a price greater than 2500.00.
You can also use the XML Extender's extracting UDFs to search on elements and attributes, when you have not created indexes or side tables for the application table. Using the extracting UDFs to scan the XML data is very expensive and should only be used with WHERE clauses that restrict the number of XML documents that are included in the search.
Example: The following statement searches with an extracting XML Extender UDF:
SELECT sales_person from sales_tab WHERE extractVarchar(order, '/Order/Customer/Name') like '%IBM%' AND invoice_num > 100
In this example, the extracting UDF extracts </Order/Customer/Name> elements with the value of IBM.
When searching on elements or attributes that have multiple occurrence, use the DISTINCT clause to prevent duplicate values.
Example: The following statement searches with the DISTINCT clause:
SELECT sales_person from sales_tab WHERE invoice_num in (SELECT DISTINCT invoice_num from part_side_tab WHERE price > 2500.00 )
In this example, the DAD file specifies that /Order/Part/Price has multiple occurrence and creates a side table PART_SIDE_TAB for it. The PART_SIDE_TAB table might have more than one row with the same invoice_num. Using DISTINCT returns only unique values.