The query_text part of the XML Schema for Database Queries to be processed by web service contains the text of the query. Attribute type specifies the type of your query: either SQL or XQUERY.
I’m going to give an example of an XQUERY using relational data.
XQuery Expressions
XQuery expressions are case-sensitive. The expressions include the following:
primary expression – literal, variable, or function application. A variable name starts with a dollar-sign ($) – for example, $foo. Literals include numerals, strings, and character or entity references.
XPath expression – Any XPath expression. The developing XPath 2.0 standard will be a subset of XQuery. XPath 1.0 is currently a subset, although XQuery uses a richer type system.
FLWOR expression – The most important XQuery expression, composed of the following, in order, from which FLWOR takes its name: for, let, where , order by, return.
XQuery sequence – The comma (,) constructor creates sequences. Sequence-manipulating functions such as union and intersect are also available. All XQuery sequences are effectively flat: a nested sequence is treated as its flattened equivalent. Thus, for instance, (1, 2, (3, 4, (5), 6), 7) is treated as (1, 2, 3, 4, 5, 6, 7). A singleton sequence, such as (42), acts the same in most XQuery contexts as does its single item, 42. Remember that the result of any XQuery expression is a sequence.
Direct (literal) constructions – XML element and attribute syntax automatically constructs elements and attributes: what you see is what you get. For example, the XQuery expression 33 constructs the XML element 33.
Computed (dynamic) constructions – You can construct XML data at runtime using computed values. For example, the following XQuery expression constructs this XML data: tata titi why? .
{attribute toto {2+3}, element bar {”tata”, “titi”}, text {” why? “}
In this example, element foo is a direct construction; the other constructions are computed. In practice, the arguments to computed constructors are not literals (such as toto and “tata”), but expressions to be evaluated (such as 2+3). Both the name and the value arguments of an element or attribute constructor can be computed. Braces ({, }) are used to mark off an XQuery expression to be evaluated.
Conditional expression – As usual, but remember that each part of the expression is itself an arbitrary expression. For instance, in this conditional expression, each of these subexpressions can be any XQuery expression: something, somethingElse, expression1, and expression2.
if (something < somethingElse) then expression1 else expression2
Arithmetic, relational expression – As usual, but remember that each relational expression returns a (BooleanFoot 1 ) value. Examples:
2 + 3
42 < $a + 5
(1, 4) = (1, 2)
5 > 3 eq true()
Quantifier expression – Universal (every) and existential (some) quantifier functions provide shortcuts to using a FLWOR expression in some cases. Examples:
every $foo in doc(”bar.xml”)//Whatever satisfies $foo/@bar > 42
some $toto in (42, 5), $titi in (”xyz12″, “abc”, 5) satisfies $toto = $titi
Regular expression – XQuery regexes are based on XML Schema 1.0 and Perl. (See Support for XQuery Functions and Operators.)
Type expression – An XQuery expression that represents an XQuery type. Examples: item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string.Foot 2
Type expressions can have occurrence indicators: ? (optional: zero or one), * (zero or more), + (one or more). Examples: document-node(element())*, item()+, attribute()?.
XQuery also provides operators for working with types. These include cast as, castable as, treat as, instance of, typeswitch, and validate. For example, “42″ cast as xs:integer is an expression whose value is the integer 2. (It is not, strictly speaking, a type expression, because its value does not represent a type.)
1
| query_text type="XQUERY"> |
XQUERY statement created in SQL Developer
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| select xmlquery(
'<regions>
{for $r in ora:view("REGIONS")
let $reg_name := $r/ROW/REGION_NAME/text()
,$reg_id := $r/ROW/REGION_ID/text()
order by $reg_id
return
<region id="{$reg_id}">
<name>{$reg_name}</name>
<countries>
{for $c in ora:view("COUNTRIES")
let $c_name := $c/ROW/COUNTRY_NAME/text()
,$c_country_id := $c/ROW/COUNTRY_ID/text()
,$c_reg_id := $c/ROW/REGION_ID/text()
where $c_reg_id = $reg_id
order by $c_country_id
return
<country id="{$c_country_id}">
<name>{$c_name}</name>
</country>
}
</countries>
</region>
}
</regions>' returning content)
from dual; |
Input XML Document for XQUERY Using Query Web Service
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:oraw="http://xmlns.oracle.com/orawsv">
<soapenv:Header/>
<soapenv:Body>
<oraw:query>
<oraw:query_text type="XQUERY">
<![CDATA[
<regions>
{for $r in ora:view("REGIONS")
let $reg_name := $r/ROW/REGION_NAME/text()
,$reg_id := $r/ROW/REGION_ID/text()
order by $reg_id
return
<region id="{$reg_id}">
<name>{$reg_name}</name>
<countries>
{for $c in ora:view("COUNTRIES")
let $c_name := $c/ROW/COUNTRY_NAME/text()
,$c_country_id := $c/ROW/COUNTRY_ID/text()
,$c_reg_id := $c/ROW/REGION_ID/text()
where $c_reg_id = $reg_id
order by $c_country_id
return
<country id="{$c_country_id}">
<name>{$c_name}</name>
</country>
}
</countries>
</region>
}
</regions>
]]>
</oraw:query_text>
<oraw:pretty_print>false</oraw:pretty_print>
</oraw:query>
</soapenv:Body>
</soapenv:Envelope> |
Output XML Document for SQL Query Using Query Web Service
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<queryOut xmlns="http://xmlns.oracle.com/orawsv">
<ROWSET>
<ROW>
<COLUMN_VALUE>
<regions>
<region id="1">
<name>Europe</name>
<countries>
<country id="BE">
<name>Belgium</name>
</country>
<country id="CH">
<name>Switzerland</name>
</country>
</countries>
</region>
<region id="2">
<name>Americas</name>
<countries>
<country id="AR">
<name>Argentina</name>
</country>
<country id="BR">
<name>Brazil</name>
</country>
</countries>
</region>
<region id="3">
<name>Asia</name>
<countries>
<country id="AU">
<name>Australia</name>
</country>
<country id="CN">
<name>China</name>
</country>
</countries>
</region>
<region id="4">
<name>Middle East and Africa</name>
<countries>
<country id="EG">
<name>Egypt</name>
</country>
<country id="IL">
<name>Israel</name>
</country>
</countries>
</region>
</regions>
</COLUMN_VALUE>
</ROW>
</ROWSET>
</queryOut>
</soap:Body>
</soap:Envelope> |
And one just for the fun of IT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:oraw="http://xmlns.oracle.com/orawsv">
<soapenv:Header/>
<soapenv:Body>
<oraw:query>
<oraw:query_text type="XQUERY">
<![CDATA[
(1, 2 + 3, "a",100 to 110, <A>33</A>)
]]>
</oraw:query_text>
<oraw:pretty_print>false</oraw:pretty_print>
</oraw:query>
</soapenv:Body>
</soapenv:Envelope> |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
| <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<queryOut xmlns="http://xmlns.oracle.com/orawsv">
<ROWSET>
<ROW>
<COLUMN_VALUE>1</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>5</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>a</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>100</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>101</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>102</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>103</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>104</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>105</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>106</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>107</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>108</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>109</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>110</COLUMN_VALUE>
</ROW>
<ROW>
<COLUMN_VALUE>
<A>33</A>
</COLUMN_VALUE>
</ROW>
</ROWSET>
</queryOut>
</soap:Body>
</soap:Envelope> |
Tags:
