This section describes searching an XML collection in terms of the following goals:
This task is actually composition using a condition. You can specify the search criteria using the following search criteria:
For example, if you enabled an XML collection, sales_ord, using the DAD file, order.dad, but you now want to override the price using form data derived from the Web, you can override the value of the <SQL_stmt> DAD element, as follows:
EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; ... EXEC SQL END DECLARE SECTION; float price_value; /* create table */ EXEC SQL CREATE TABLE xml_order_tab (xmlorder XMLVarchar); /* initialize host variable and indicators */ strcpy(collection,"sales_ord"); strcpy(result_tab,"xml_order_tab"); overrideType = SQL_OVERRIDE; max_row = 20; num_row = 0; returnCode = 0; msg_txt[0] = '\0'; override_ind = 0; overrideType_ind = 0; rtab_ind = 0; maxrow_ind = 0; numrow_ind = -1; returnCode_ind = -1; returnMsg_ind = -1; /* get the price_value from some place, such as form data */ price_value = 1000.00 /* for example*/ /* specify the overwrite */ sprintf(overwrite, "SELECT o.order_key, customer, p.part_key, quantity, price, tax, ship_id, date, mode FROM order_tab o, part_tab p, table(select substr(char(timestamp(generate_unique())),16) as ship_id, date, mode from ship_tab)as s WHERE p.price > %d and s.date >'1996-06-01' AND p.order_key = o.order_key and s.part_key = p.part_key", price_value); /* Call the store procedure */ EXEC SQL CALL db2xml!dxxRetrieve(:collection:collection_ind, :result_tab:rtab_ind, :overrideType:overrideType_ind,:overwrite:overwrite_ind, :max_row:maxrow_ind,:num_row:numrow_ind, :returnCode:returnCode_ind,:returnMsg:returnMsg_ind);
The condition of price > 2500.00 in order.dad is overridden by price > ?, where ? is based on the input variable price_value.
You can use normal SQL query operations to search collection tables. You can join collection tables, or use subqueries, and then do structural-text search on text columns. With the results of the structural search, you can apply that data to retrieve or generate the specified XML document.