Skip Headers
Oracle® Fusion Middleware CQL Language Reference for Oracle Event Processing
11g Release 1 (11.1.1.7)

Part Number E12048-09
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 Expressions

This chapter provides a reference to expressions in Oracle Continuous Query Language (Oracle CQL). An expression is a combination of one or more values and one or more operations, including a constant having a definite value, a function that evaluates to a value, or an attribute containing a value.

Every expression maps to a datatype. This simple expression evaluates to 4 and has datatype NUMBER (the same datatype as its components):

2*2 

The following expression is an example of a more complex expression that uses both functions and operators. The expression adds seven days to the current date, removes the time component from the sum, and converts the result to CHAR datatype:

TO_CHAR(TRUNC(SYSDATE+7)) 

This chapter includes the following section:

5.1 Introduction to Expressions

Oracle Event Processing provides the following expressions:

You can use expressions in:

Oracle Event Processing does not accept all forms of expressions in all parts of all Oracle CQL statements. Refer to the individual Oracle CQL statements in Chapter 22, "Oracle CQL Statements" for information on restrictions on the expressions in that statement.

You must use appropriate expression notation whenever expr appears in conditions, Oracle CQL functions, or Oracle CQL statements in other parts of this reference. The sections that follow describe and provide examples of the various forms of expressions.

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform Oracle Event Processing that no further input will have a timestamp lesser than the heartbeat value.


aggr_distinct_expr

Use an aggr_distinct_expr aggregate expression when you want to use an aggregate built-in function with distinct. When you want to use an aggregate built-in function without distinct, see "aggr_expr".

aggr_distinct_expr::=

Surrounding text describes aggr_distinct_expr.png.

(arith_expr::=)

You can specify an arith_distinct_expr as the argument of an aggregate expression.

You can use an aggr_distinct_expr in the following Oracle CQL statements:

For more information, see Chapter 9, "Built-In Aggregate Functions".

Examples

Example 5-2 shows how to use a COUNT aggregate distinct expression.

Example 5-1 aggr_distinct_expr for COUNT

create view viewq2Cond1(ACCT_INTRL_ID, sumForeign, countForeign) as 
         select ACCT_INTRL_ID, sum(TRXN_BASE_AM), count(distinct ADDR_CNTRY_CD)
         from ValidCashForeignTxn[range 48 hours]
         group by ACCT_INTRL_ID 
         having ((sum(TRXN_BASE_AM) * 100) >= (1000 * 60) and
                 (count(distinct ADDR_CNTRY_CD >= 2)))

aggr_expr

Use an aggr_expr aggregate expression when you want to use aggregate built-in functions. When you want to use an aggregate built-in function with distinct, see "aggr_distinct_expr"

aggr_expr::=

Surrounding text describes aggr_expr.png.

(arith_expr::=, identifier::=, attr::=, xml_agg_expr::=)

You can specify an arith_expr as the argument of an aggregate expression.

The count aggregate built-in function takes a single argument made up of any of the values that Table 5-1 lists and returns the int value indicated.

Table 5-1 Return Values for COUNT Aggregate Function

Input Argument Return Value

arith_expr

The number of tuples where arith_expr is not null.

*

The number of all tuples, including duplicates and nulls.

identifier.*

The number of all tuples that match the correlation variable identifier, including duplicates and nulls.

identifier.attr

The number of tuples that match correlation variable identifier, where attr is not null.


The first and last aggregate built-in functions take a single argument made up of the following period separated values:

You can use an aggr_expr in the following Oracle CQL statements:

For more information, see:

Examples

Example 5-2 shows how to use a COUNT aggregate expression.

Example 5-2 aggr_expr for COUNT

<view id="SegVol" schema="exp_way lane dir seg volume"><![CDATA[ 
    select 
        exp_way, 
        lane, 
        dir, 
        seg, 
        count(*) as volume 
    from 
        CurCarSeg 
    group by 
        exp_way,
        lane, 
        dir, 
        seg 
    having 
        count(*) > 50
]]></view>

arith_expr

Use an arith_expr arithmetic expression to define an arithmetic expression using any combination of stream element attribute values, constant values, the results of a function expression, aggregate built-in function, case expression, or decode. You can use all of the normal arithmetic operators (+,-,*, and /) and the concatenate operator (||).

arith_expr::=

Surrounding text describes arith_expr.png.

(attr::=, const_value::=, func_expr::=, aggr_expr::=, aggr_distinct_expr::=, case_expr::=, decode::=, arith_expr::=)

You can use an arith_expr in the following Oracle CQL statements:

For more information, see "Arithmetic Operators".

Examples

Example 5-3 shows how to use a arith_expr expression.

Example 5-3 arith_expr

<view id="SegVol" schema="exp_way lane dir seg volume"><![CDATA[ 
    select 
        exp_way, 
        speed * 1.5 as adjustedSpeed, 
        dir, 
        max(seg) as maxSeg, 
        count(*) as volume 
    from 
        CurCarSeg 
    having 
        adjustedSpeed > 50
]]></view>

arith_expr_list

Use an arith_expr_list arithmetic expression list to define one or more arithmetic expressions using any combination of stream element attribute values, constant values, the results of a function expression, aggregate built-in function, case expression, or decode. You can use all of the normal arithmetic operators (+,-,*, and /) and the concatenate operator (||).

arith_expr_list::=

Surrounding text describes arith_expr_list.png.

(arith_expr::=)

You can use an arith_expr_list in the following Oracle CQL statements:

For more information, see "Arithmetic Operators".

Examples

Example 5-4 shows how to use a arith_expr_list expression.

Example 5-4 arith_expr_list

<query id="q1"><![CDATA[ 
    select
        XMLELEMENT("Emp", XMLELEMENT("Name", e.job_id||' '||e.last_name),            XMLELEMENT("Hiredate", e.hire_date)
        )
    from 
        tkdata51_S0 [range 1] as e
]]></query>

case_expr

Use a case_expr case expression to evaluate stream elements against multiple conditions.

case_expr::=

Surrounding text describes case_expr.png.

(searched_case_list::=, arith_expr::=, simple_case_list::=)

searched_case_list::=

Surrounding text describes searched_case_list.png.

(searched_case::=)

searched_case::=

Surrounding text describes searched_case.png.

(non_mt_cond_list::=, arith_expr::=)

simple_case_list::=

Surrounding text describes simple_case_list.png.

(simple_case::=)

simple_case::=

Surrounding text describes simple_case.png.

(arith_expr::=)

The case_expr is similar to the DECODE clause of an arithmetic expression (see "decode").

In a searched_case clause, when the non_mt_cond_list evaluates to true, the searched_case clause may return either an arithmetic expression or null.

In a simple_case clause, when the arithmetic expression is true, the simple_case clause may return either another arithmetic expression or null.

You can use an case_expr in the following Oracle CQL statements:

Examples

This section describes the following case_expr examples:

case_expr with SELECT *

Consider the query q97 in Example 5-5 and the data stream S0 in Example 5-6. Stream S1 has schema (c1 float, c2 integer). The query returns the relation in Example 5-7.

Example 5-5 CASE Expression: SELECT * Query

<query id="q97"><![CDATA[ 
    select * from S0 
    where 
        case 
            when c2 < 25 then c2+5 
            when c2 > 25 then c2+10 
        end > 25
]]></query>

Example 5-6 CASE Expression: SELECT * Stream Input

Timestamp   Tuple
     1000    0.1,10
     1002   0.14,15
   200000    0.2,20
   400000    0.3,30
   500000    0.3,35
   600000       ,35
h 800000
100000000   4.04,40
h 200000000

Example 5-7 CASE Expression: SELECT * Relation Output

Timestamp   Tuple Kind  Tuple
400000:+ 0.3,30
500000:+ 0.3,35
600000:+ ,35
100000000:+ 4.04,40

case_expr with SELECT

Consider the query q96 in Example 5-8 and the data streams S0 in Example 5-9 and S1 in Example 5-10. Stream S0 has schema (c1 float, c2 integer) and stream S1 has schema (c1 float, c2 integer). The query returns the relation in Example 5-11.

Example 5-8 CASE Expression: SELECT Query

<query id="q96"><![CDATA[ 
    select 
        case to_float(S0.c2+10) 
            when (S1.c2*100)+10 then S0.c1+0.5 
            when (S1.c2*100)+11 then S0.c1 
            else S0.c1+0.3 
        end 
    from 
        S0[rows 100], 
        S1[rows 100]
]]></query>

Example 5-9 CASE Expression: SELECT Stream S0 Input

Timestamp   Tuple
     1000    0.1,10
     1002   0.14,15
   200000    0.2,20
   400000    0.3,30
   500000    0.3,35
   600000       ,35
h 800000
100000000   4.04,40
h 200000000

Example 5-10 CASE Expression: SELECT Stream S1 Input

Timestamp   Tuple
     1000   10,0.1
     1002   15,0.14
   200000   20,0.2
   300000     ,0.2
   400000   30,0.3
100000000   40,4.04

Example 5-11 CASE Expression: SELECT Relation Output

Timestamp   Tuple Kind  Tuple
     1000:  +           0.6
     1002:  +           0.44
     1002:  +           0.4
     1002:  +           0.14
   200000:  +           0.5
   200000:  +           0.5
   200000:  +           0.4
   200000:  +           0.44
   200000:  +           0.7
   300000:  +           0.4
   300000:  +           0.44
   300000:  +           0.7
   400000:  +           0.6
   400000:  +           0.6
   400000:  +           0.6
   400000:  +           0.6
   400000:  +           0.4
   400000:  +           0.44
   400000:  +           0.5
   400000:  +           0.8
   500000:  +           0.6
   500000:  +           0.6
   500000:  +           0.6
   500000:  +           0.6
   500000:  +           0.6
   600000:  +
   600000:  +
   600000:  +
   600000:  +
   600000:  +
100000000:  +           4.34
100000000:  +           4.34
100000000:  +           4.34
100000000:  +           4.34
100000000:  +           4.34
100000000:  +           0.4
100000000:  +           0.44
100000000:  +           0.5
100000000:  +           0.6
100000000:  +           0.6
100000000:  +
100000000:  +           4.34

decode

Use a decode expression to evaluate stream elements against multiple conditions.

decode::=

Surrounding text describes decode.png.

(non_mt_arg_list::=)

DECODE expects its non_mt_arg_list to be of the form:

expr, search1, result1, search2, result2, ... , searchN, result N, default

DECODE compares expr to each search value one by one. If expr equals a search value, the DECODE expressions returns the corresponding result. If no match is found, the DECODE expressions returns default. If default is omitted, the DECODE expressions returns null.

The arguments can be any of the numeric (INTEGER, BIGINT, FLOAT, or DOUBLE) or character (CHAR) datatypes. For more information, see Section 2.1, "Datatypes").

The search, result, and default values can be derived from expressions. Oracle Event Processing uses short-circuit evaluation. It evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle Event Processing never evaluates a search i, if a previous search j (0 < j < i) equals expr.

Oracle Event Processing automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle Event Processing automatically converts the return value to the same datatype as the first result.

In a DECODE expression, Oracle Event Processing considers two nulls to be equivalent. If expr is null, then Oracle Event Processing returns the result of the first search that is also null.

The maximum number of components in the DECODE expression, including expr, searches, results, and default, is 255.

The decode expression is similar to the case_expr (see case_expr::=).

You can use a decode expression in the following Oracle CQL statements:

Examples

Consider the query q in Example 5-12 and the input relation R in Example 5-13. Relation R has schema (c1 float, c2 integer). The query returns the relation in Example 5-14.

Example 5-12 Arithmetic Expression and DECODE Query

<query id="q"><![CDATA[ 
...
    SELECT DECODE (c2, 10, c1+0.5, 20, c1+0.1, 30, c1+0.2, c1+0.3) from R
]]></query>

Example 5-13 Arithmetic Expression and DECODE Relation Input

Timestamp   Tuple Kind  Tuple
     1000:  +            0.1,10
     1002:  +           0.14,15
     2000:  -            0.1,10
     2002:  -           0.14,15
   200000:  +            0.2,20
   201000:  -            0.2,20
   400000:  +            0.3,30
   401000:  -            0.3,30
   500000:  +            0.3,35
   501000:  -            0.3,35
   600000:  +               ,35
   601000:  -               ,35
100000000:  +           4.04,40
100001000:  -           4.04,40

Example 5-14 Arithmetic Expression and DECODE Relation Output

Timestamp   Tuple Kind  Tuple
     1000:  +           0.6
     1002:  +           0.44
   200000:  +           0.3
   400000:  +           0.5
   500000:  +           0.6
100000000:  +           4.34

func_expr

Use the func_expr function expression to define a function invocation using any Oracle CQL built-in, user-defined, or Oracle data cartridge function.

func_expr::=

Surrounding text describes func_expr.png.

(identifier::=, const_int::=, const_bigint::=, const_string::=, xqryargs_list::=, non_mt_arg_list::=, xml_parse_expr::=, xmlelement_expr::=, xmlforest_expr::=, xmlcolattval_expr::=, func_name:=, link::=, arith_expr::=)

func_name:=

Surrounding text describes func_name.png.

(identifier::=)

func_name

You can specify the identifier of a function explicitly:

PREV

The PREV function takes a single argument made up of the following period-separated identifier arguments:

The PREV function also takes the following const_int arguments:

For more information, see "prev". For an example, see "func_expr PREV Function Example".

XQuery: XMLEXISTS and XMLQUERY

You can specify an XQuery that Oracle Event Processing applies to the XML stream element data that you bind in xqryargs_list. For more information, see:

An xqryargs_list is a comma separated list of one or more xqryarg instances made up of an arithmetic expression involving one or more stream elements from the select list, the AS keyword, and a const_string that represents the XQuery variable or operator (such as the "." current node operator). For more information, see xqryargs_list::=.

For an example, see "func_expr XMLQUERY Function Example".

For more information, see "SQL/XML (SQLX)".

XMLCONCAT

The XMLCONCAT function returns the concatenation of its comma-delimited xmltype arguments as an xmltype.

For more information, see:

SQL/XML (SQLX)

The SQLX specification extends SQL to support XML data.

Oracle CQL supports event types containing properties of type SQLX. In this case, Oracle Event Processing server converts from SQLX to String when within Oracle CQL, and converts from String to SQLX on output.

Oracle CQL provides the following expressions (and functions) to manipulate data from an SQLX stream. For example, you can construct XML elements or attributes with SQLX stream elements, combine XML fragments into larger ones, and parse input into XML content or documents.

Note:

Oracle CQL does not support external relations with columns of type XMLTYPE (for example, a join with a relational database management system). For more information, see Section 2.1.1, "Oracle CQL Built-in Datatypes".

For more information on Oracle CQL SQLX expressions, see:

For more information on Oracle CQL SQLX functions, see:

For more information on datatype restrictions when using Oracle CQL with XML, see:

For more information on SQLX, see the Oracle Database SQL Language Reference.

FIRST and LAST

The FIRST and LAST functions each take a single argument made up of the following period-separated values:

For more information, see:

You can specify the identifier of a function explicitly with or without a non_mt_arg_list: a list of arguments appropriate for the built-in or user-defined function being invoked. The list can have single or multiple arguments.

You can use a func_expr in the following Oracle CQL statements:

For more information, see Section 1.1.11, "Functions".

Examples

This section describes the following func_expr examples:

func_expr PREV Function Example

Example 5-15 shows how to compose a func_expr to invoke the PREV function.

Example 5-15 func_expr for PREV

<query id="q36"><![CDATA[ 
    select T.Ac1 from S15 
    MATCH_RECOGNIZE ( 
        PARTITION BY
            c2 
        MEASURES 
            A.c1 as Ac1 
        PATTERN(A) 
        DEFINE 
            A as (A.c1 = PREV(A.c1,3,5000) )
    ) as T
]]></query>

func_expr XMLQUERY Function Example

Example 5-16 shows how to compose a func_expr to invoke the XMLQUERY function.

Example 5-16 func_expr for XMLQUERY

<query id="q1"><![CDATA[ 
    select
        xmlexists(
            "for $i in /PDRecord where $i/PDId <= $x return $i/PDName" 
            passing by value 
                c2 as ".", 
                (c1+1) as "x" 
            returning content
        ) xmldata
    from
        S1
]]></query>

Example 5-17 shows how to compose a func_expr to invoke the SUM function.

Example 5-17 func_expr for SUM

<query id="q3"><![CDATA[ 
    select sum(c2) from S1[range 5]
]]></query>

object_expr

Use the object_expr expression to reference the members of a data cartridge complex type.

You can use an object_expr anywhere an arithmetic expression can be used. For more information, see "arith_expr".

object_expr::=

For syntax, see:

Optionally, you can use a link (@) in the object_expr to specify the data cartridge name. Use a link to specify the location of an Oracle CQL data cartridge complex type class, method, field, or constructor to disambiguate the reference, if necessary. The location must reference a data cartridge by its name. For example, if two data cartridges (myCartridge and yourCartridge) both define a complex type com.package.ThisClass, then you must use the link clause to explicitly identify which com.package.ThisClass you want to use.

Note:

A link is not required when using the types that the default Java data cartridge provides.

link::=

Surrounding text describes link.png.

(methodname::=, data_cartridge_name::=, param_list::=, qualified_type_name::=)

data_cartridge_name::=

Surrounding text describes data_cartridge_name.png.

(identifier::=)

data_cartridge_name

Each Oracle CQL data cartridge implementation is identified by a unique data cartridge name.

Data cartridge names include:

For syntax, see data_cartridge_name::= (parent: link::=).

Type Declaration

You declare an event property as a complex type using qualified_type_name@data_cartridge_name.

For examples, see "Type Declaration Example: link"

Field Access

You cannot specify a link when accessing a complex type field because the type of the field already identifies its location. The following is not allowed:

SELECT java.lang.String(“foo”).CASE_INSENSITIVE_ORDER@java …

For examples, see "Field Access Example: link".

Method Access

You cannot specify a link when accessing complex type method because the type of the method already identifies its location. The following is not allowed:

SELECT java.lang.String(“foo”).substring@java(0,1) …

For examples, see "Method Access Example: link".

Constructor Invocation

You invoke a complex type constructor using qualified_type_name@data_cartridge_name(param_list).

For examples, see "Constructor Invocation Example: link".

Examples

The following examples illustrate the various semantics that this statement supports:

Object Expression Example

Example 5-18 shows object_expr:

getContainingGeometries@spatial (InputPoints.point)

This object_expr uses a data cartridge TABLE clause that invokes the Oracle Spatial method getContainingGeometries, passing in one parameter (InputPoints.point). The return value of this method, a Collection of Oracle Event Processing IType records, is aliased as validGeometries. The table source itself is aliased as R2.

Example 5-18 Data Cartridge TABLE Query

<query id="q1"><![CDATA[ 
RSTREAM (
    SELECT 
        R2.validGeometries.shape as containingGeometry,
        R1.point as inputPoint
    FROM 
        InputPoints[now] as R1,
        TABLE (getContainingGeometries@spatial (InputPoints.point) as validGeometries) AS R2
)
]]></query>

Type Declaration Example: link

Example 5-19 shows how to create an event type as a Java class that specifies an event property as an Oracle CQL data cartridge complex type MyType defined in package com.mypackage that belongs to the Oracle CQL data cartridge myCartridge. If a com.myPackage.MyType is defined in some other Oracle CQL data cartridge (with data cartridge name otherCatridge), specifying the type for the a1 property using a link with the data cartridge name myCartridge allows Oracle CQL to reference the correct complex type.

Example 5-19 Type Declaration Using an Oracle CQL Data Cartridge Link

package com.myapplication.event;

import java.util.Date;
import // Oracle CQL data cartridge package(s)?

public final class MarketEvent {
    private final String symbol;
    private final Double price;
    private final com.myPackage.MyType@myCartridge a1;

    public MarketEvent(...) {
        ...
        }
    ...
}

Field Access Example: link

Example 5-20 shows how to instantiate complex type MyType and access the static field MY_FIELD. The link clause explicitly references the com.myPackage.MyType class that belongs to the Oracle CQL data cartridge myCartridge.

Example 5-20 Field Access Using an Oracle CQL Data Cartridge Link

<query id="q1"><![CDATA[ 
    SELECT com.myPackage.MyType@myCartridge(“foo”).MY_FIELD ...
]]></query>

Method Access Example: link

Example 5-21 shows how to instantiate complex type MyType and access the method myMethod. The link clause explicitly references the com.myPackage.MyType class that belongs to the Oracle CQL data cartridge myCartridge.

Example 5-21 Type Declaration Using an Oracle CQL Data Cartridge Link

<query id="q1"><![CDATA[ 
    SELECT com.myPackage.MyType@myCartridge(“foo”).myMethod("bar") ...
]]></query>

Constructor Invocation Example: link

Example 5-22 shows how to instantiate complex type MyType. The link clause explicitly references the com.myPackage.MyType class that belongs to the Oracle CQL data cartridge myCartridge.

Example 5-22 Type Declaration Using an Oracle CQL Data Cartridge Link

<query id="q1"><![CDATA[ 
    SELECT com.myPackage.MyType@myCartridge(“foo”) ...
]]></query>

order_expr

Use the order_expr expression to specify the sort order in which Oracle Event Processing returns tuples that a query selects.

order_expr::=

Surrounding text describes order_expr.png.

(attr::=, const_int::=)

You can specify a stream element by attr name.

Alternatively, you can specify a stream element by its const_int index where the index corresponds to the stream element position you specify at the time you register or create the stream.

You can use an order_expr in the following Oracle CQL statements:

Examples

Consider Example 5-23. Stream S3 has schema (c1 bigint, c2 interval, c3 byte(10), c4 float). This example shows how to order the results of query q210 by c1 and then c2 and how to order the results of query q211 by c2, then by the stream element at index 3 (c3) and then by the stream element at index 4 (c4).

Example 5-23 order_expr

<query id="q210"><![CDATA[ 
    select * from S3 order by c1 desc nulls first, c2 desc nulls last
]]></query>
<query id="q211"><![CDATA[ 
    select * from S3 order by c2 desc nulls first, 3 desc nulls last, 4 desc
]]></query>

xml_agg_expr

Use an xml_agg_expr expression to return a collection of XML fragments as an aggregated XML document. Arguments that return null are dropped from the result.

xml_agg_expr::=

Surrounding text describes xml_agg_expr.png.

(arith_expr, order_by_clause::=)

You can specify an xml_agg_expr as the argument of an aggregate expression.

You can use an xml_agg_expr in the following Oracle CQL statements:

For more information, see:

Examples

Consider the query tkdata67_q1 in Example 5-24 and the input relation tkdata67_S0 in Example 5-25. Relation tkdata67_S0 has schema (c1 integer, c2 float). The query returns the relation in Example 5-26.

Example 5-24 xml_agg_expr Query

<query id="tkdata67_q1"><![CDATA[ 
    select 
        c1, 
        xmlagg(xmlelement("c2",c2)) 
    from 
        tkdata67_S0[rows 10] 
    group by c1
]]></query>

Example 5-25 xml_agg_expr Relation Input

Timestamp           Tuple
 1000               15, 0.1
 1000               20, 0.14
 1000               15, 0.2
 4000               20, 0.3
10000               15, 0.04
h 12000

Example 5-26 xml_agg_expr Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           15,<c2>0.1</c2>
                           <c2>0.2</c2>
1000:       +           20,<c2>0.14</c2>
4000:       -           20,<c2>0.14</c2>
4000:       +           20,<c2>0.14</c2>
                           <c2>0.3</c2>
10000:      -           15,<c2>0.1</c2>
                           <c2>0.2</c2>
10000:      +           15,<c2>0.1</c2>
                           <c2>0.2</c2>
                           <c2>0.04</c2>

xmlcolattval_expr

Use an xmlcolattval_expr expression to create an XML fragment and then expand the resulting XML so that each XML fragment has the name column with the attribute name.

xmlcolattval_expr::=

Surrounding text describes xmlcolattval_expr.png.

(xml_attr_list::=)

You can specify an xmlcolattval_expr as the argument of a function expression. It is especially useful when processing SQLX streams. For more information, see "SQL/XML (SQLX)".

You can use an xmlcolattval_expr in the following Oracle CQL statements:

For more information, see "XMLCOLATTVAL" in the Oracle Database SQL Language Reference.

Examples

Consider the query tkdata53_q1 in Example 5-24 and the input relation tkdata53_S0 in Example 5-25. Relation tkdata53_S0 has schema (c1 integer, c2 float). The query returns the relation in Example 5-26.

Example 5-27 xmlcolattval_expr Query

<query id="tkdata53_q1"><![CDATA[ 
    select 
        XMLELEMENT("tkdata53_S0", XMLCOLATTVAL( tkdata53_S0.c1, tkdata53_S0.c2)) 
    from 
        tkdata53_S0 [range 1]
]]></query>

Example 5-28 xmlcolattval_expr Relation Input

Timestamp                Tuple
     1000:               10, 0.1
     1002:               15, 0.14
   200000:               20, 0.2
   400000:               30, 0.3
h 800000
100000000:               40, 4.04
h 200000000

Example 5-29 xmlcolattval_expr Relation Output

Timestamp   Tuple Kind  Tuple
     1000:       +      <tkdata53_S0>
                            <column name="c1">10</column>
                            <column name="c2">0.1</column>
                        </tkdata53_S0>
     1002:       +      <tkdata53_S0>
                            <column name="c1">15</column>
                            <column name="c2">0.14</column>
                        </tkdata53_S0>
     2000:       -      <tkdata53_S0>
                            <column name="c1">10</column>
                            <column name="c2">0.1</column>
                        </tkdata53_S0>
     2002:       -      <tkdata53_S0>
                            <column name="c1">15</column>
                            <column name="c2">0.14</column>
                        </tkdata53_S0>
   200000:       +      <tkdata53_S0>
                            <column name="c1">20</column>
                            <column name="c2">0.2</column>
                        </tkdata53_S0>
   201000:       -      <tkdata53_S0>
                            <column name="c1">20</column>
                            <column name="c2">0.2</column>
                        </tkdata53_S0>
   400000:       +      <tkdata53_S0>
                            <column name="c1">30</column>
                            <column name="c2">0.3</column>
                        </tkdata53_S0>
   401000:       -      <tkdata53_S0>
                            <column name="c1">30</column>
                            <column name="c2">0.3</column>
                        </tkdata53_S0>
100000000:       +      <tkdata53_S0>
                            <column name="c1">40</column>
                            <column name="c2">4.04</column>
                        </tkdata53_S0>
100001000:       -      <tkdata53_S0>
                            <column name="c1">40</column>
                            <column name="c2">4.04</column>
                        </tkdata53_S0>

xmlelement_expr

Use an xmlelement_expr expression when you want to construct a well-formed XML element from stream elements.

xmlelement_expr::=

Surrounding text describes xmlelement_expr.png.

(const_string::=, arith_expr::=, xml_attribute_list::=, arith_expr_list::=)

You can specify an xmlelement_expr as the argument of a function expression. It is especially useful when processing SQLX streams. For more information, see "SQL/XML (SQLX)".

You can use an xmlelement_expr in the following Oracle CQL statements:

For more information, see "XMLELEMENT" in the Oracle Database SQL Language Reference.

Examples

Consider the query tkdata51_q0 in Example 5-30 and the input relation tkdata51_S0 in Example 5-31. Relation tkdata51_S0 has schema (c1 integer, c2 float). The query returns the relation in Example 5-32.

Example 5-30 xmlelement_expr Query

<query id="tkdata51_q0"><![CDATA[ 
    select 
        XMLELEMENT(
            NAME "S0", 
            XMLELEMENT(NAME "c1", tkdata51_S0.c1), 
            XMLELEMENT(NAME "c2", tkdata51_S0.c2)
        ) 
    from 
        tkdata51_S0 [range 1]
]]></query>

Example 5-31 xmlelement_expr Relation Input

Timestamp                Tuple
     1000:               10, 0.1
     1002:               15, 0.14
   200000:               20, 0.2
   400000:               30, 0.3
h 800000
100000000:               40, 4.04
h 200000000

Example 5-32 xmlelement_expr Relation Output

Timestamp   Tuple Kind  Tuple
     1000:  +           <S0>
                            <c1>10</c1>
                            <c2>0.1</c2>
                        </S0>
     1002:  +           <S0>
                            <c1>15</c1>
                            <c2>0.14</c2>
                        </S0>
     2000:  -           <S0>
                            <c1>10</c1>
                            <c2>0.1</c2>
                        </S0>
     2002:  -           <S0>
                            <c1>15</c1>
                            <c2>0.14</c2>
                        </S0>
   200000:  +           <S0>
                            <c1>20</c1>
                            <c2>0.2</c2>
                        </S0>
   201000:  -           <S0>
                            <c1>20</c1>
                            <c2>0.2</c2>
                        </S0>
   400000:  +           <S0>
                            <c1>30</c1>
                            <c2>0.3</c2>
                        </S0>
   401000:  -           <S0>
                            <c1>30</c1>
                            <c2>0.3</c2>
                        </S0>
100000000:  +           <S0>
                            <c1>40</c1>
                            <c2>4.04</c2>
                        </S0>
100001000:  -           <S0>
                            <c1>40</c1>
                            <c2>4.04</c2>
                        </S0>

xmlforest_expr

Use an xmlforest_expr to convert each of its argument parameters to XML, and then return an XML fragment that is the concatenation of these converted arguments.

xmlforest_expr::=

Surrounding text describes xmlforest_expr.png.

(xml_attr_list::=)

You can specify an xmlforest_expr as the argument of a function expression. It is especially useful when processing SQLX streams. For more information, see "SQL/XML (SQLX)".

You can use an xmlforest_expr in the following Oracle CQL statements:

For more information, see " XMLFOREST" in the Oracle Database SQL Language Reference.

Examples

Consider the query tkdata52_q0 in Example 5-33 and the input relation tkdata52_S0 in Example 5-34. Relation tkdata52_S0 has schema (c1 integer, c2 float). The query returns the relation in Example 5-35.

Example 5-33 xmlforest_expr Query

<query id="tkdata52_q0"><![CDATA[ 
    select 
        XMLFOREST( tkdata52_S0.c1, tkdata52_S0.c2) 
    from 
        tkdata52_S0 [range 1]
]]></query>

Example 5-34 xmlforest_expr Relation Input

Timestamp                Tuple
     1000:               10, 0.1
     1002:               15, 0.14
   200000:               20, 0.2
   400000:               30, 0.3
h 800000
100000000:               40, 4.04
h 200000000

Example 5-35 xmlforest_expr Relation Output

Timestamp   Tuple Kind  Tuple
     1000:  +           <c1>10</c1>
                        <c2>0.1</c2>
     1002:  +           <c1>15</c1>
                        <c2>0.14</c2>
     2000:  -           <c1>10</c1>
                        <c2>0.1</c2>
     2002:  -           <c1>15</c1>
                        <c2>0.14</c2>
   200000:  +           <c1>20</c1>
                        <c2>0.2</c2>
   201000:  -           <c1>20</c1>
                        <c2>0.2</c2>
   400000:  +           <c1>30</c1>
                        <c2>0.3</c2>
   401000:  -           <c1>30</c1>
                        <c2>0.3</c2>
100000000:  +           <c1>40</c1>
                        <c2>4.04</c2>
100001000:  -           <c1>40</c1>
                        <c2>4.04</c2>

xml_parse_expr

Use an xml_parse_expr expression to parse and generate an XML instance from the evaluated result of arith_expr.

xml_parse_expr::=

Surrounding text describes xml_parse_expr.png.

(arith_expr::=)

When using an xml_parse_expr expression, note the following:

You can specify an xml_parse_expr as the argument of a function expression. It is especially useful when processing SQLX streams. For more information, see "SQL/XML (SQLX)".

You can use an xml_parse_expr in the following Oracle CQL statements:

For more information, see " XMLPARSE" in the Oracle Database SQL Language Reference.

Examples

This section describes the following xml_parse_expr examples:

xml_parse_expr Content Example

Consider the query tkdata62_q3 in Example 5-36 and the input relation tkdata62_S1 in Example 5-37. Relation tkdata62_S1 has schema (c1 char(30)). The query returns the relation in Example 5-38.

Example 5-36 xml_parse_expr Content: Query

<query id="tkdata62_q3"><![CDATA[ 
    select XMLPARSE(CONTENT c1) from tkdata62_S1
]]></query>

Example 5-37 xml_parse_expr Content: Relation Input

Timestamp               Tuple
1000                    "<a>3</a>"
1000                    "<e3>blaaaaa</e3>"
1000                    "<r>4</r>"
1000                    "<a></a>
1003                    "<a>s3</a>"
1004                    "<d>b6</d>"

Example 5-38 xml_parse_expr Content: Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +     <a>3</a>
1000:       +     <e3>blaaaaa</e3>
1000:       +     <r>4</r>
1000:       +     <a/>
1003:       +     <a>s3</a>
1004:       +     <d>b6</d>

xml_parse_expr Document Example

Consider the query tkdata62_q4 in Example 5-39 and the input relation tkdata62_S1 in Example 5-40. Relation tkdata62_S1 has schema (c1 char(30)). The query returns the relation in Example 5-41.

Example 5-39 xml_parse_expr Document: Query

<query id="tkdata62_q4"><![CDATA[ 
    select XMLPARSE(DOCUMENT c1) from tkdata62_S1
]]></query>

Example 5-40 xml_parse_expr Document: Relation Input

Timestamp               Tuple
1000                    "<a>3</a>"
1000                    "<e3>blaaaaa</e3>"
1000                    "<r>4</r>"
1000                    "<a></a>
1003                    "<a>s3</a>"
1004                    "<d>b6</d>"

Example 5-41 xml_parse_expr Document: Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +     <a>3</a>
1000:       +     <e3>blaaaaa</e3>
1000:       +     <r>4</r>
1000:       +     <a/>
1003:       +     <a>s3</a>
1004:       +     <d>b6</d>

xml_parse_expr Wellformed Example

Consider the query tkdata62_q2 in Example 5-42 and the input relation tkdata62_S in Example 5-43. Relation tkdata62_S has schema (c char(30)). The query returns the relation in Example 5-44.

Example 5-42 xml_parse_expr Wellformed: Query

<query id="tkdata62_q2"><![CDATA[ 
    select XMLPARSE(DOCUMENT c WELLFORMED) from tkdata62_S
]]></query>

Example 5-43 xml_parse_expr Wellformed: Relation Input

Timestamp               Tuple
1000                    "<a>3</a>"
1000                    "<e3>blaaaaa</e3>"
1000                    "<r>4</r>"
1000                    "<a/>"
1003                    "<a>s3</a>"
1004                    "<d>b6</d>"

Example 5-44 xml_parse_expr Wellformed: Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           <a>3</a>
1000:       +           <e3>blaaaaa</e3>
1000:       +           <r>4</r>
1000:       +           <a/>
1003:       +           <a>s3</a>
1004:       +           <d>b6</d>