ResultSetXml contains two methods, allString( ) and someString( ), for quantified searches on columns of a ResultSetXML document. To illustrate these two methods, first create some example rows in the order_results table.
The order_results table has been initialized with one row, whose id = “1” and whose rs_doc column contains the original Order used in all examples.
The following statements copy that row twice, assigning id values of “2” and “3” to the new rows. The order_results table now has three rows, with id column values of “1,” “2,” and “3” and the original Order.
insert into resultset_docs(id, rs_doc)
select "2", rs_doc from resultset_docs where id="1"
insert into resultset_docs (id, rs_doc) select "3", rs_doc from resultset_docs where id="1"
The following statements modify the row with an id column value of “1” so that all three items have ItemIds of “100”, “110”, and “120”:
update resultset_docs set rs_doc = rs_doc>>setColumn(0, "ItemId", "100") where id="1"
update resultset_docs set rs_doc = rs_doc>>setColumn(1, "ItemId", "110") where id="1"
update resultset_docs set rs_doc = rs_doc>>setColumn(2, "ItemId", "120") where id="1"
The following update statement modifies the row with id = “3” so that its second item (from 0) has an ItemId of “999”:
update resultset_docs set rs_doc = rs_doc>>setColumn(2, "ItemId", "999") where id="3"
The following select statement displays the id column and the three ItemId values for each row:
select id, rs_doc>>getColumn(0, "ItemId"), rs_doc>>getColumn(1, "ItemId"), rs_doc>>getColumn(2, "ItemId") from resultset_docs
The results of the select are:
1 100 110 120
2 987 654 579
3 987 654 999
Note the following:
The row with id of “2” is the original Order data.
The row with id of “1” has been modified so that all ItemIds for that row are less than “200.”
The row with id of “3” has been modified so that some ItemId for that row is greater than or equal to “9999”
The following expresses these quantifications with the allString( ) and someString( ) methods:
select id, rs_doc>>allString(3, "<", "200") as “ALL test” from resultset_docs
select id, rs_doc>>someString(3, ">=", "999") as “SOME test” from resultset_docs
select id as “id for ALL test” from resultset_docs where rs_doc>>allString(3, "<", "200")>>booleanValue() = 1
select id as “id for SOME test” from resultset_docs where rs_doc>>someString(3, ">=", "999")>>booleanValue() = 1
The first two statements show the quantifier in the select list and give these results:
ID |
“all” test |
“some” test |
|---|---|---|
1 |
true |
false |
2 |
false |
false |
3 |
false |
true |
The last two statements show the quantifier in the where clause and give these results:
ID for “all” test = “3”
ID for “some” test = “1”
In the examples with the quantifier method in the where clause, note that:
The where clause examples compare the method results with an integer value of 1. SQL does not support the Boolean datatypes as a function value, but instead treats Boolean as equivalent to integer values 1 and 0, for true and false.
The where clause examples use the booleanValue( ) method. The allString( ) and someString( ) methods return type java.lang.Boolean, which is not compatible with SQL integer. The Java booleanValue( ) method returns the simple Boolean value from the Boolean object, which is compatible with SQL integer. This behavior is a result of merging the SQL and Java type systems.
The quantifier methods return java.lang.Boolean instead of simply Java boolean so that they can return null when the column is out of range, which is consistent with the SQL treatment of out-of-range conditions.
The following statements show quantifier references that specify column 33, which does not exist in the data:
select id, rs_doc>>allString(33, "<", "200") as “ALL test” from resultset_docs
select id as “id for ALL test” from resultset_docs where rs_doc>>allString(33, "<", "200")>>booleanValue() = 1
ID |
“all” test |
|---|---|
1 |
NULL |
2 |
NULL |
3 |
NULL |
The ID for the “all” test = (empty).
