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

8 Built-In Single-Row Functions

This chapter provides a reference to single-row functions in Oracle Continuous Query Language (Oracle CQL). Single-row functions return a single result row for every row of a queried stream or view.

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

This chapter includes the following section:

8.1 Introduction to Oracle CQL Built-In Single-Row Functions

Table 8-1 lists the built-in single-row functions that Oracle CQL provides.

Table 8-1 Oracle CQL Built-in Single-Row Functions

Type Function

Character (returning character values)

Character (returning numeric values)

Datetime

Conversion

XML and SQLX

Encoding and Decoding

Null-related

Pattern Matching


Note:

Built-in function names are case sensitive and you must use them in the case shown (in lower case).

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.

For more information, see:


concat

Syntax

Surrounding text describes concat.png.

Purpose

concat returns char1 concatenated with char2 as a char[] or byte1 concatenated with byte2 as a byte[]. The char returned is in the same character set as char1. Its datatype depends on the datatypes of the arguments.

Using concat, you can concatenate any combination of character, byte, and numeric datatypes. The concat performs automatic numeric to string conversion.

This function is equivalent to the concatenation operator (||). For more information, see "Concatenation Operator".

To concatenate xmltype arguments, use xmlconcat. For more information, see "xmlconcat".

Examples

concat Function

Consider the query chr_concat in Example 8-1 and data stream S4 in Example 8-2. Stream S4 has schema (c1 char(10)). The query returns the relation in Example 8-3.

Example 8-1 concat Function Query

<query id="chr_concat"><![CDATA[ 
    select 
        concat(c1,c1),
        concat("abc",c1),
        concat(c1,"abc") 
    from 
        S4[range 5]
]]></query>

Example 8-2 concat Function Stream Input

Timestamp   Tuple
 1000
 2000       hi
 8000       hi1
 9000
15000       xyz
h 200000000

Example 8-3 concat Function Relation Output

Timestamp   Tuple Kind  Tuple
 1000:      +           ,abc,abc
 2000:      +           hihi,abchi,hiabc
 6000:      -           ,abc,abc
 7000:      -           hihi,abchi,hiabc
 8000:      +           hi1hi1,abchi1,hi1abc
 9000:      +           ,abc,abc
13000:      -           hi1hi1,abchi1,hi1abc
14000:      -           ,abc,abc
15000:      +           xyzxyz,abcxyz,xyzabc
20000:      -           xyzxyz,abcxyz,xyzabc

Concatenation Operator (||)

Consider the query q264 in Example 8-4 and the data stream S10 in Example 8-5. Stream S10 has schema (c1 integer, c2 char(10)). The query returns the relation in Example 8-6.

Example 8-4 Concatenation Operator (||) Query

<query id="q264"><![CDATA[ 
    select
        c2 || "xyz"
    from
        S10
]]></query>

Example 8-5 Concatenation Operator (||) Stream Input

Timestamp   Tuple
1           1,abc
2           2,ab
3           3,abc
4           4,a
h 200000000

Example 8-6 Concatenation Operator (||) Relation Output

Timestamp   Tuple Kind  Tuple
1:          +           abcxyz
2:          +           abxyz
3:          +           abcxyz
4:          +           axyz

hextoraw

Syntax

Surrounding text describes hextoraw.png.

Purpose

hextoraw converts char containing hexadecimal digits in the char character set to a raw value.

See Also:

"rawtohex"

Examples

Consider the query q6 in Example 8-7 and the data stream SinpByte1 in Example 8-8. Stream SinpByte1 has schema (c1 byte(10), c2 integer). The query returns the relation in Example 8-9.

Example 8-7 hextoraw Function Query

<query id="q6"><![CDATA[ 
    select * from SByt[range 2] 
    where 
        bytTest(c2) between hextoraw("5200") and hextoraw("5600")
]]></query>

Example 8-8 hextoraw Function Stream Input

Timestamp   Tuple
1000        1,"51c1"
2000        2,"52"
3000        3,"53aa"
4000        4,"5"
5000         ,"55ef"
6000        6,
h 8000
h 200000000

Example 8-9 hextoraw Function Relation Output

Timestamp   Tuple Kind  Tuple
2000        +           2,"52"
3000        +           3,"53aa"
4000        -           2,"52"
5000        -           3,"53aa"
5000        +            ,"55ef"
7000        -            ,"55ef"

length

Syntax

Surrounding text describes length.png.

Purpose

The length function returns the length of its char or byte expression as an int. length calculates length using characters as defined by the input character set.

For a char expression, the length includes all trailing blanks. If the expression is null, this function returns null.

Examples

Consider the query chr_len in Example 8-10 and the data stream S2 in Example 8-11. Stream S2 has schema (c1 integer, c2 integer). The query returns the relation that Example 8-12.

Example 8-10 length Function Query

<query id="chr_len"><![CDATA[ 
    select length(c1) from S4[range 5]
]]></query>

Example 8-11 length Function Stream Input

Timestamp   Tuple
 1000
 2000       hi
 8000       hi1
 9000
15000       xyz
h 200000000

Example 8-12 length Function Relation Output

Timestamp   Tuple Kind  Tuple
 1000:      +           0
 2000:      +           2
 6000:      -           0
 7000:      -           2
 8000:      +           3
 9000:      +           0
13000:      -           3
14000:      -           0
15000:      +           3
20000:      -           3

lk

Syntax

Surrounding text describes lk.png.

Purpose

lk boolean true if char1 matches the regular expression char2, otherwise it returns false.

This function is equivalent to the LIKE condition. For more information, see Section 6.4, "LIKE Condition".

Examples

Consider the query q291 in Example 8-13 and the data stream SLk1 in Example 8-14. Stream SLk1 has schema (first1 char(20), last1 char(20)). The query returns the relation in Example 8-15.

Example 8-13 lk Function Query

<query id="q291"><![CDATA[ 
    select * from SLk1 
    where 
        lk(first1,"^Ste(v|ph)en$") = true
]]></query>

Example 8-14 lk Function Stream Input

Timestamp   Tuple
1           Steven,King
2           Sten,Harley
3           Stephen,Stiles
4           Steven,Markles
h 200000000

Example 8-15 lk Function Relation Output

Timestamp   Tuple Kind  Tuple
1:          +           Steven,King
3:          +           Stephen,Stiles
4:          +           Steven,Markles

nvl

Syntax

Surrounding text describes nvl.png.

Purpose

nvl lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Event Processing implicitly converts one to the other. If they cannot be converted implicitly, Oracle Event Processing returns an error. The implicit conversion is implemented as follows:

Examples

Consider the query q281 in Example 8-16 and the data stream SNVL in Example 8-17. Stream SNVL has schema (c1 char(20), c2 integer). The query returns the relation in Example 8-18.

Example 8-16 nvl Function Query

<query id="q281"><![CDATA[ 
    select nvl(c1,"abcd") from SNVL
]]></query>

Example 8-17 nvl Function Stream Input

Timestamp   Tuple
1              ,1
2            ab,2
3           abc,3
4              ,4
h 200000000

Example 8-18 nvl Function Relation Output

Timestamp   Tuple Kind  Tuple
1:          +           abcd
2:          +           ab
3:          +           abc
4:          +           abcd

prev

Syntax

Surrounding text describes prev.png.

Purpose

prev returns the value of the stream attribute (function argument identifier2) of the event that occurred previous to the current event and which belongs to the partition to which the current event belongs. It evaluates to NULL if there is no such previous event.

The type of the specified stream element may be any of:

The return type of this function depends on the type of the specified stream attribute (function argument identifier2).

This function takes the following arguments:

Where:

If the query uses PARTITION BY with the prev function and input data will include many different partition key values (meaning many partitions), then total memory consumed for storing the previous event(s) per partition could be large. In such cases, consider using the time range duration (the third argument, possibly with a large range value) so that this memory can be reclaimed wherever possible.

Examples

prev(identifier1.identifier2)

Consider query q2 in Example 8-19 and the data stream S1 in Example 8-20. Stream S1 has schema (c1 integer). This example defines pattern A as A.c1 = prev(A.c1). In other words, pattern A matches when the value of c1 in the current stream element matches the value of c1 in the stream element immediately before the current stream element. The query returns the stream in Example 8-21.

Example 8-19 prev(identifier1.identifier2) Function Query

<query id="q2"><![CDATA[ 
    select 
        T.Ac1,
        T.Cc1 
    from 
        S1 
    MATCH_RECOGNIZE ( 
        MEASURES 
            A.c1 as Ac1, 
            C.c1 as Cc1 
        PATTERN(A B+ C) 
        DEFINE 
            A as A.c1 = prev(A.c1), 
            B as B.c1 = 10, 
            C as C.c1 = 7
    ) as T
]]></query>

Example 8-20 prev(identifier1.identifier2) Function Stream Input

Timestamp   Tuple
1000        35
3000        35
4000        10
5000         7

Example 8-21 prev(identifier1.identifier2) Function Stream Output

Timestamp   Tuple Kind  Tuple
5000:       +           35,7

prev(identifier1.identifier2, const_int)

Consider query q35 in Example 8-22 and the data stream S15 in Example 8-23. Stream S15 has schema (c1 integer, c2 integer). This example defines pattern A as A.c1 = prev(A.c1,3). In other words, pattern A matches when the value of c1 in the current stream element matches the value of c1 in the third stream element before the current stream element. The query returns the stream in Example 8-24.

Example 8-22 prev(identifier1.identifier2, const_int) Function Query

<query id="q35"><![CDATA[ 
    select T.Ac1 from S15 
    MATCH_RECOGNIZE ( 
        MEASURES 
            A.c1 as Ac1 
        PATTERN(A) 
        DEFINE 
            A as (A.c1 = prev(A.c1,3) )
    ) as T
]]></query>

Example 8-23 prev(identifier1.identifier2, const_int) Function Stream Input

Timestamp   Tuple
 1000       45,20
 2000       45,30
 3000       45,30
 4000       45,30
 5000       45,30
 6000       45,20
 7000       45,20
 8000       45,20
 9000       43,40
10000       52,10
11000       52,30
12000       43,40
13000       52,50
14000       43,40
15000       43,40

Example 8-24 prev(identifier1.identifier2, const_int) Function Stream Output

Timestamp   Tuple Kind  Tuple
 4000:       +          45
 5000:       +          45
 6000:       +          45
 7000:       +          45
 8000:       +          45
12000:       +          43
13000:       +          52
15000:       +          43

prev(identifier1.identifier2, const_int, const_bigint)

Consider query q36 in Example 8-26 and the data stream S15 in Example 8-27. Stream S15 has schema (c1 integer, c2 integer). This example defines pattern A as A.c1 = prev(A.c1,3,5000000000L). In other words, pattern A matches when:

The query returns the output stream that Example 8-28 shows. Notice that in the output stream, there is no output at 8000. Example 8-25 shows the contents of the partition (partitioned by the value of the c2 attribute) to which the event at 8000 belongs.

Example 8-25 Partition Containing the Event at 8000

Timestamp   Tuple
1000        45,20
6000        45,20
7000        45,20
8000        45,20

As Example 8-25 shows, even though the value of c1 in the third previous event (the event at 1000) is the same as the value c1 in the current event (the event at 8000), the range condition is not satisfied. This is because the difference in the timestamps of these two events is more than 5000000000 nanoseconds. So it is treated as if there is no previous tuple and prev returns NULL so the condition fails to match.

Example 8-26 prev(identifier1.identifier2, const_int, const_bigint) Function Query

<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,5000000000L) )
    ) as T
]]></query>

Example 8-27 prev(identifier1.identifier2, const_int, const_bigint) Function Stream Input

Timestamp   Tuple
 1000       45,20
 2000       45,30
 3000       45,30
 4000       45,30
 5000       45,30
 6000       45,20
 7000       45,20
 8000       45,20
 9000       43,40
10000       52,10
11000       52,30
12000       43,40
13000       52,50
14000       43,40
15000       43,40

Example 8-28 prev(identifier1.identifier2, const_int, const_bigint) Function Stream Output

Timestamp   Tuple Kind  Tuple
5000:       +           45

rawtohex

Syntax

Surrounding text describes rawtohex.png.

Purpose

rawtohex converts byte containing a raw value to hexadecimal digits in the CHAR character set.

See Also:

"hextoraw"

Examples

Consider the query byte_to_hex in Example 8-29 and the data stream S5 in Example 8-30. Stream S5 has schema (c1 integer, c2 byte(10)). This query uses the rawtohex function to convert a ten byte raw value to the equivalent ten hexidecimal digits in the character set of your current locale. The query returns the relation in Example 8-31.

Example 8-29 rawtohex Function Query

<query id="byte_to_hex"><![CDATA[ 
    select rawtohex(c2) from S5[range 4]
]]></query>

Example 8-30 rawtohex Function Stream Input

Timestamp   Tuple
1000        1,"51c1"
2000        2,"52"
2500        7,"axc"
3000        3,"53aa"
4000        4,"5"
5000         ,"55ef"
6000        6,
h 8000
h 200000000

Example 8-31 rawtohex Function Relation Output

Timestamp   Tuple Kind  Tuple
 1000:      +           51c1
 2000:      +           52
 3000:      +           53aa
 4000:      +           05
 5000:      -           51c1
 5000:      +           55ef
 6000:      -           52
 6000:      +
 7000:      -           53aa
 8000:      -           05
 9000:      -           55ef
10000:      -

systimestamp

Syntax

Surrounding text describes systimestamp.png.

Purpose

systimestamp returns the system date, including fractional seconds and time zone, of the system on which the Oracle Event Processing server resides. The return type is TIMESTAMP WITH TIME ZONE.

Examples

Consider the query q106 in Example 8-32 and the data stream S0 in Example 8-33. Stream S0 has schema (c1 float, c2 integer). The query returns the relation in Example 8-34. For more information about case, see "case_expr".

Example 8-32 systimestamp Function Query

<query id="q106"><![CDATA[ 
    select * from S0 
    where 
        case c2 
            when 10 then null 
            when 20 then null 
            else systimestamp() 
        end > "07/06/2007 14:13:33"
]]></query>

Example 8-33 systimestamp Function 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 8-34 systimestamp Function Relation Output

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

to_bigint

Syntax

Surrounding text describes to_bigint.png.

Purpose

to_bigint returns a bigint number equivalent of its integer argument.

For more information, see:

Examples

Consider the query q282 in Example 8-35 and the data stream S11 in Example 8-36. Stream S11 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-37.

Example 8-35 to_bigint Function Query

<query id="q282"><![CDATA[ 
    select nvl(to_bigint(c1), 5.2) from S11
]]></query>

Example 8-36 to_bigint Function Stream Input

Timestamp   Tuple
  10        1,abc
2000         ,ab
3400        3,abc
4700         ,a
h 8000
h 200000000

Example 8-37 to_bigint Function Relation Output

Timestamp   Tuple Kind  Tuple
  10:       +           1
2000:       +           5.2
3400:       +           3
4700:       +           5.2

to_boolean

Syntax

Surrounding text describes to_boolean.png.

Purpose

to_boolean returns a value of true or false for its bigint or integer expression argument.

For more information, see:

Examples

Consider the query q282 in Example 8-35 and the data stream S11 in Example 8-36. Stream S11 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-37.

Example 8-38 to_boolean Function Query

<view id="v2" schema="c1 c2" ><![CDATA[
    select to_boolean(c1), c1 from tkboolean_S3 [now] where c2 = 0.1
]]></view><query id="q1"><![CDATA[ 
    select * from v2
]]></query>

Example 8-39 to_boolean Function Stream Input

Timestamp   Tuple
1000        -2147483648, 0.1
2000        2147483647, 0.2
3000        12345678901, 0.3
4000        -12345678901, 0.1
5000        9223372036854775799, 0.2
6000        -9223372036854775799, 0.3
7000        , 0.1
8000        10000000000, 0.2
9000        60000000000, 0.3
h 200000000

Example 8-40 to_boolean Function Relation Output

Timestamp   Tuple Kind  Tuple
1000      +           true,-2147483648
1000      -           true,-2147483648
4000      +           true,-12345678901
4000      -           true,-12345678901
7000      +           ,
7000      -           ,

to_char

Syntax

Surrounding text describes to_char.png.

Purpose

to_char returns a char value for its integer, double, bigint, float, timestamp, or interval expression argument. If the bigint argument exceeds the char precision, Oracle Event Processing returns an error.

For more information, see:

Examples

Consider the query q282 in Example 8-35 and the data stream S11 in Example 8-36. Stream S11 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-37.

Example 8-41 to_char Function Query

<query id="q1"><![CDATA[ 
    select to_char(c1), to_char(c2), to_char(c3), to_char(c4), to_char(c5), to_char(c6) 
    from S1
]]></query>

Example 8-42 to_char Function Stream Input

Timestamp   Tuple
1000        99,99999, 99.9, 99.9999, "4 1:13:48.10", "08/07/2004 11:13:48", cep

Example 8-43 to_char Function Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           99,99999,99.9,99.9999,4 1:13:48.10,08/07/2004 11:13:48

to_double

Syntax

Surrounding text describes to_double.png.

Purpose

to_double returns a double value for its bigint, integer, or float expression argument. If the bigint argument exceeds the double precision, Oracle Event Processing returns an error.

For more information, see:

Examples

Consider the query q282 in Example 8-35 and the data stream S11 in Example 8-36. Stream S11 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-37.

Example 8-44 to_double Function Query

<query id="q282"><![CDATA[ 
    select nvl(to_double(c1), 5.2) from S11
]]></query>

Example 8-45 to_double Function Stream Input

Timestamp   Tuple
  10        1,abc
2000         ,ab
3400        3,abc
4700         ,a
h 8000
h 200000000

Example 8-46 to_double Function Relation Output

Timestamp   Tuple Kind  Tuple
  10:       +           1
2000:       +           5.2
3400:       +           3
4700:       +           5.2

to_float

Syntax

Surrounding text describes to_float.png.

Purpose

to_float returns a float number equivalent of its bigint or integer argument. If the bigint argument exceeds the float precision, Oracle Event Processing returns an error.

For more information, see:

Examples

Consider the query q1 in Example 8-47 and the data stream S11 in Example 8-48. Stream S1 has schema (c1 integer, name char(10)). The query returns the relation in Example 8-49.

Example 8-47 to_float Function Query

<query id="q1"><![CDATA[ 
    select nvl(to_float(c1), 5.2) from S11
]]></query>

Example 8-48 to_float Function Stream Input

Timestamp   Tuple
  10        1, abc
2000         , ab
3400        3, abc
4700         , a
h 8000
h 200000000

Example 8-49 to_float Function Relation Output

Timestamp   Tuple Kind  Tuple
10:+ 1.02000:+ 5.23400:+ 3.04700:+ 5.2

to_timestamp

Syntax

Surrounding text describes to_timestamp.png.

Purpose

to_timestamp converts char literals that conform to java.text.SimpleDateFormat format models to timestamp datatypes. There are two forms of the to_timestamp function distinguished by the number of arguments:

For more information, see:

Examples

Consider the query q277 in Example 8-50 and the data stream STs2 in Example 8-51. Stream STs2 has schema (c1 integer, c2 char(20)). The query returns the relation that Example 8-52.

Example 8-50 to_timestamp Function Query

<query id="q277"><![CDATA[ 
    select * from STs2 
    where 
        to_timestamp(c2,"yyMMddHHmmss") = to_timestamp("09/07/2005 10:13:48")
]]></query>

Example 8-51 to_timestamp Function Stream Input

Timestamp   Tuple
1           1,"040807111348"
2           2,"050907101348"
3           3,"041007111348"
4           4,"060806111248"
h 200000000

Example 8-52 to_timestamp Function Relation Output

Timestamp   Tuple Kind  Tuple
2:          +           2,050907101348

xmlcomment

Syntax

Surrounding text describes xmlcomment.png.

Purpose

xmlcomment returns its double-quote delimited constant String argument as an xmltype.

Using xmlcomment, you can add a well-formed XML comment to your query results.

This function takes the following arguments:

The return type of this function is xmltype. The exact schema depends on that of the input stream of XML data.

Examples

Consider the query tkdata64_q1 in Example 8-53 and data stream tkdata64_S in Example 8-54. Stream tkdata64_S has schema (c1 char(30)). The query returns the relation in Example 8-55.

Example 8-53 xmlcomment Function Query

<query id="tkdata64_q1"><![CDATA[ 
    xmlconcat(xmlelement("parent", c1), xmlcomment("this is a comment")) 
from tkdata64_S
]]></query>

Example 8-54 xmlcomment Function Stream Input

Timestamp   Tuple
c 30
1000        "san jose"
1000        "mountain view"
1000
1000        "sunnyvale"
1003
1004        "belmont"

Example 8-55 xmlcomment Function Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           <parent>san jose</parent>
                        <!--this is a comment-->
1000:       +           <parent>mountain view</parent>
                        <!--this is a comment-->
1000:       +           <parent/>
                        <!--this is a comment-->
1000:       +           <parent>sunnyvale</parent>
                        <!--this is a comment-->
1003:       +           <parent/>
                        <!--this is a comment-->
1004:       +           <parent>belmont</parent>
                        <!--this is a comment-->

xmlconcat

Syntax

Surrounding text describes xmlconcat.png.

Purpose

xmlconcat returns the concatenation of its comma-delimited xmltype arguments as an xmltype.

Using xmlconcat, you can concatenate any combination of xmltype arguments.

This function takes the following arguments:

The return type of this function is xmltype. The exact schema depends on that of the input stream of XML data.

This function is especially useful when processing SQLX streams. For more information, see "SQL/XML (SQLX)".

To concatenate datatypes other than xmltype, use CONCAT. For more information, see "concat".

See Also:

"SQL/XML (SQLX)"

Examples

Consider the query tkdata64_q1 in Example 8-53 and the data stream tkdata64_S in Example 8-54. Stream tkdata64_S has schema (c1 char(30)). The query returns the relation in Example 8-55.

Example 8-56 xmlconcat Function Query

<query id="tkdata64_q1"><![CDATA[ 
    select 
        xmlconcat(xmlelement("parent", c1), xmlcomment("this is a comment")) 
    from tkdata64_S
]]></query>

Example 8-57 xmlconcat Function Stream Input

Timestamp   Tuple
c 30
1000        "san jose"
1000        "mountain view"
1000
1000        "sunnyvale"
1003
1004        "belmont"

Example 8-58 xmlconcat Function Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           <parent>san jose</parent>
                        <!--this is a comment-->
1000:       +           <parent>mountain view</parent>
                        <!--this is a comment-->
1000:       +           <parent/>
                        <!--this is a comment-->
1000:       +           <parent>sunnyvale</parent>
                        <!--this is a comment-->
1003:       +           <parent/>
                        <!--this is a comment-->
1004:       +           <parent>belmont</parent>
                        <!--this is a comment-->

xmlexists

Syntax

Surrounding text describes xmlexists.png.

Purpose

xmlexists creates a continuous query against a stream of XML data to return a boolean that indicates whether or not the XML data satisfies the XQuery you specify.

This function takes the following arguments:

The return type of this function is boolean: true if the XQuery is satisfied; false otherwise.

This function is especially useful when processing SQLX streams. For more information, see "SQL/XML (SQLX)".

Examples

Consider the query q1 in Example 8-59 and the XML data stream S in Example 8-60. Stream S has schema (c1 integer, c2 xmltype). In this example, the value of stream element c2 is bound to the current node (".") and the value of stream element c1 + 1 is bound to XQuery variable x. The query returns the relation in Example 8-61.

Example 8-59 xmlexists Function Query

<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 
        S
]]></query>

Example 8-60 xmlexists Function Stream Input

Timestamp   Tuple
3           1, "<PDRecord><PDName>hello</PDName></PDRecord>"
4           2, "<PDRecord><PDName>hello</PDName><PDName>hello1</PDName></PDRecord>"
5           3, "<PDRecord><PDId>6</PDId><PDName>hello1</PDName></PDRecord>"
6           4, "<PDRecord><PDId>46</PDId><PDName>hello2</PDName></PDRecord>"

Example 8-61 xmlexists Function Relation Output

Timestamp   Tuple Kind  Tuple
3:          +           false
4:          +           false
5:          +           true
6:          +           false

xmlquery

Syntax

Surrounding text describes xmlquery.png.

Purpose

xmlquery creates a continuous query against a stream of XML data to return the XML data that satisfies the XQuery you specify.

This function takes the following arguments:

The return type of this function is xmltype. The exact schema depends on that of the input stream of XML data.

This function is especially useful when processing SQLX streams. For more information, see "SQL/XML (SQLX)".

Examples

Consider the query q1 in Example 8-62 and the XML data stream S in Example 8-63. Stream S has schema (c1 integer, c2 xmltype). In this example, the value of stream element c2 is bound to the current node (".") and the value of stream element c1 + 1 is bound to XQuery variable x. The query returns the relation in Example 8-64.

Example 8-62 xmlquery Function Query

<query id="q1"><![CDATA[ 
    SELECT 
        xmlquery(
            "for $i in /PDRecord WHERE $i/PDId <= $x RETURN $i/PDName" 
            PASSING BY VALUE  
                c2 as ".", 
               (c1+1) AS "x" 
            RETURNING CONTENT
        ) XMLData 
    FROM 
        S
]]></query>

Example 8-63 xmlquery Function Stream Input

Timestamp   Tuple
3           1, "<PDRecord><PDName>hello</PDName></PDRecord>"
4           2, "<PDRecord><PDName>hello</PDName><PDName>hello1</PDName></PDRecord>"
5           3, "<PDRecord><PDId>6</PDId><PDName>hello1</PDName></PDRecord>"
6           4, "<PDRecord><PDId>46</PDId><PDName>hello2</PDName></PDRecord>"

Example 8-64 xmlquery Function Relation Output

Timestamp   Tuple Kind  Tuple
3:          +
4:          +
5:          +           "<PDName>hello1</PDName>"
6:          +           "<PDName>hello2</PDName>"