ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
create table t0 (si smallint, i int, bi bigint, vcb varchar (32) for bit data, nu numeric(10,2), f float, d double, vc varchar(20), da date, ti time, ts timestamp, cl clob, bl blob);
0 rows inserted/updated/deleted
ij> -- XML column declarations should work like other built-in types.
create table t1 (i int, x xml);
0 rows inserted/updated/deleted
ij> create table t2 (i int, x xml not null);
0 rows inserted/updated/deleted
ij> create table t3 (i int, x xml default null);
0 rows inserted/updated/deleted
ij> create table t4 (vc varchar(100));
0 rows inserted/updated/deleted
ij> create table t5 (x2 xml not null);
0 rows inserted/updated/deleted
ij> alter table t5 add column x1 xml;
0 rows inserted/updated/deleted
ij> -- Check insertion of null XML values.
----- Next four should work.
insert into t1 values (1, null);
1 row inserted/updated/deleted
ij> insert into t1 values (2, cast (null as xml));
1 row inserted/updated/deleted
ij> insert into t1 (i) values (4);
1 row inserted/updated/deleted
ij> insert into t1 values (3, default);
1 row inserted/updated/deleted
ij> -- Next two should fail.
insert into t2 values (1, null);
ERROR 23502: Column 'X' cannot accept a NULL value.
ij> insert into t2 values (2, cast (null as xml));
ERROR 23502: Column 'X' cannot accept a NULL value.
ij> -- XML cols can't hold non-XML types.
insert into t1 values (3, 'hmm');
ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.
ij> insert into t1 values (1, 2);
ERROR 42821: Columns of type 'XML' cannot hold values of type 'INTEGER'.
ij> insert into t1 values (1, 123.456);
ERROR 42821: Columns of type 'XML' cannot hold values of type 'DECIMAL'.
ij> insert into t1 values (1, x'01');
ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR () FOR BIT DATA'.
ij> insert into t1 values (1, x'ab');
ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR () FOR BIT DATA'.
ij> insert into t1 values (1, current date);
ERROR 42821: Columns of type 'XML' cannot hold values of type 'DATE'.
ij> insert into t1 values (1, current time);
ERROR 42821: Columns of type 'XML' cannot hold values of type 'TIME'.
ij> insert into t1 values (1, current timestamp);
ERROR 42821: Columns of type 'XML' cannot hold values of type 'TIMESTAMP'.
ij> insert into t1 values (1, ('hmm' | | 'andstuff'));
ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.
ij> -- XML can't be stored in non-XML cols.
insert into t0 (si) values (cast (null as xml));
ERROR 42821: Columns of type 'SMALLINT' cannot hold values of type 'XML'.
ij> insert into t0 (i) values (cast (null as xml));
ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'XML'.
ij> insert into t0 (bi) values (cast (null as xml));
ERROR 42821: Columns of type 'BIGINT' cannot hold values of type 'XML'.
ij> insert into t0 (vcb) values (cast (null as xml));
ERROR 42821: Columns of type 'VARCHAR () FOR BIT DATA' cannot hold values of type 'XML'.
ij> insert into t0 (nu) values (cast (null as xml));
ERROR 42821: Columns of type 'NUMERIC' cannot hold values of type 'XML'.
ij> insert into t0 (f) values (cast (null as xml));
ERROR 42821: Columns of type 'DOUBLE' cannot hold values of type 'XML'.
ij> insert into t0 (d) values (cast (null as xml));
ERROR 42821: Columns of type 'DOUBLE' cannot hold values of type 'XML'.
ij> insert into t0 (vc) values (cast (null as xml));
ERROR 42821: Columns of type 'VARCHAR' cannot hold values of type 'XML'.
ij> insert into t0 (da) values (cast (null as xml));
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'XML'.
ij> insert into t0 (ti) values (cast (null as xml));
ERROR 42821: Columns of type 'TIME' cannot hold values of type 'XML'.
ij> insert into t0 (ts) values (cast (null as xml));
ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'XML'.
ij> insert into t0 (cl) values (cast (null as xml));
ERROR 42821: Columns of type 'CLOB' cannot hold values of type 'XML'.
ij> insert into t0 (bl) values (cast (null as xml));
ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'XML'.
ij> -- No casting is allowed.
insert into t1 values (1, cast ('hmm' as xml));
ERROR 42846: Cannot convert types 'CHAR' to 'XML'.
ij> insert into t1 values (1, cast (2 as xml));
ERROR 42846: Cannot convert types 'INTEGER' to 'XML'.
ij> insert into t1 values (1, cast (123.456 as xml));
ERROR 42846: Cannot convert types 'DECIMAL' to 'XML'.
ij> insert into t1 values (1, cast (x'01' as xml));
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'XML'.
ij> insert into t1 values (1, cast (x'ab' as xml));
ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'XML'.
ij> insert into t1 values (1, cast (current date as xml));
ERROR 42846: Cannot convert types 'DATE' to 'XML'.
ij> insert into t1 values (1, cast (current time as xml));
ERROR 42846: Cannot convert types 'TIME' to 'XML'.
ij> insert into t1 values (1, cast (current timestamp as xml));
ERROR 42846: Cannot convert types 'TIMESTAMP' to 'XML'.
ij> insert into t1 values (1, cast (('hmm' | | 'andstuff') as xml));
ERROR 42846: Cannot convert types 'CHAR' to 'XML'.
ij> -- XML can't be used in non-XML operations.
select i + x from t1;
ERROR 42Y95: The '+' operator with a left operand type of 'INTEGER' and a right operand type of 'XML' is not supported.
ij> select i * x from t1;
ERROR 42Y95: The '*' operator with a left operand type of 'INTEGER' and a right operand type of 'XML' is not supported.
ij> select i / x from t1;
ERROR 42Y95: The '/' operator with a left operand type of 'INTEGER' and a right operand type of 'XML' is not supported.
ij> select i - x from t1;
ERROR 42Y95: The '-' operator with a left operand type of 'INTEGER' and a right operand type of 'XML' is not supported.
ij> select -x from t1;
ERROR 42X37: The unary '-' operator is not allowed on the 'XML' type.
ij> select 'hi' | | x from t1;
ERROR 42846: Cannot convert types 'XML' to 'VARCHAR'.
ij> select substr(x, 0) from t1;
ERROR 42X25: The 'SUBSTR' function is not allowed on the 'XML' type.
ij> select i from t1 where x like 'hmm';
ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found.
ij> select max(x) from t1;
ERROR 42Y22: Aggregate MAX cannot operate on type XML.
ij> select min(x) from t1;
ERROR 42Y22: Aggregate MIN cannot operate on type XML.
ij> select length(x) from t1;
ERROR 42X25: The 'length' function is not allowed on the 'XML' type.
ij> -- Comparsions against XML don't work.
select i from t1 where x = 'hmm';
ERROR 42818: Comparisons between 'XML' and 'CHAR' are not supported.
ij> select i from t1 where x > 0;
ERROR 42818: Comparisons between 'XML' and 'INTEGER' are not supported.
ij> select i from t1 where x > x;
ERROR 42818: Comparisons between 'XML' and 'XML' are not supported.
ij> select i from t1 where x > 'some char';
ERROR 42818: Comparisons between 'XML' and 'CHAR' are not supported.
ij> -- Indexing/ordering on XML cols is not allowed.
create index oops_ix on t1(x);
ERROR X0X67: Columns of type 'XML' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> select i from t1 where x is null order by x;
ERROR X0X67: Columns of type 'XML' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- XML cannot be imported or exported. These should all fail.
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (
null, 'T1', 'xmlexport.del', null, null, null);
ERROR 38000: The exception 'java.sql.SQLException: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.' was thrown while evaluating an expression. SQLSTATE: 42Z71: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.
ij> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY(
'select x from t1', 'xmlexport.del', null, null, null);
ERROR 38000: The exception 'java.sql.SQLException: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.' was thrown while evaluating an expression. SQLSTATE: 42Z71: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.
ij> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY (
'select xmlserialize(x as clob) from t1',
'xmlexport.del', null, null, null);
ERROR XIE0B: Column '1' in the table is of type CLOB, it is not supported by the import/export feature.
ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (
null, 'T1', 'shouldntmatter.del', null, null, null, 0);
ERROR XIE0B: Column 'X' in the table is of type XML, it is not supported by the import/export feature.
ij> CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (
NULL, 'T1', null, '2', 'shouldntmatter.del', null, null, null,0);
ERROR XIE0B: Column 'X' in the table is of type XML, it is not supported by the import/export feature.
ij> -- XML cannot be used with procedures/functions.
create procedure hmmproc (in i int, in x xml)
parameter style java language java external name 'hi.there';
ERROR 42962: Long column type column or parameter 'X' not permitted in declared global temporary tables or procedure definitions.
ij> create function hmmfunc (i int, x xml) returns int
parameter style java language java external name 'hi.there';
ERROR 42962: Long column type column or parameter 'X' not permitted in declared global temporary tables or procedure definitions.
ij> -- XML columns cannot be used for global temporary tables.
declare global temporary table SESSION.xglobal (myx XML)
not logged on commit preserve rows;
ERROR 42962: Long column type column or parameter 'MYX' not permitted in declared global temporary tables or procedure definitions.
ij> -- XML cols can be used in a SET clause, if target value is XML.
create trigger tr2 after insert on t1 for each row mode db2sql update t1 set x = 'hmm';
ERROR 42821: Columns of type 'XML' cannot hold values of type 'CHAR'.
ij> create trigger tr1 after insert on t1 for each row mode db2sql update t1 set x = null;
0 rows inserted/updated/deleted
ij> drop trigger tr1;
0 rows inserted/updated/deleted
ij> -- Test XMLPARSE operator.
----- These should fail.
insert into t1 values (1, xmlparse(document '' strip whitespace));
ERROR 42Z74: XML feature not supported: 'STRIP WHITESPACE'.
ij> insert into t1 values (1, xmlparse(document ''));
ERROR 42Z72: Missing SQL/XML keyword(s) 'PRESERVE WHITESPACE' at line 1, column 53.
ij> insert into t1 values (1, xmlparse('' preserve whitespace));
ERROR 42Z72: Missing SQL/XML keyword(s) 'DOCUMENT' at line 1, column 36.
ij> insert into t1 values (1, xmlparse(content '' preserve whitespace));
ERROR 42Z74: XML feature not supported: 'CONTENT'.
ij> select xmlparse(document xmlparse(document '' preserve whitespace) preserve whitespace) from t1;
ERROR 42X25: The 'XMLParse' function is not allowed on the 'XML' type.
ij> select i from t1 where xmlparse(document '' preserve whitespace);
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'XML' expression. It must be a BOOLEAN expression.
ij> insert into t1 values (1, xmlparse(document '' preserve whitespace));
ERROR 2200M: Invalid XML DOCUMENT: XML document structures must start and end within the same entity. SQLSTATE: XJ001: Java exception: 'XML document structures must start and end within the same entity.: xxxFILTERED-SAX-EXCEPTIONxxx'.
ij> prepare ps1 as 'insert into t1(x) values XMLPARSE(document ? preserve whitespace)';
ERROR 42Z79: Unable to determine the parameter type for XMLPARSE; try using a CAST.
ij> -- These should work.
insert into t1 values (5, xmlparse(document '' preserve whitespace));
1 row inserted/updated/deleted
ij> insert into t1 values (6, xmlparse(document ' bass boosted. ' preserve whitespace));
1 row inserted/updated/deleted
ij> insert into t2 values (1, xmlparse(document ' work as planned ' preserve whitespace));
1 row inserted/updated/deleted
ij> insert into t5 (x1, x2) values (null, xmlparse(document '' preserve whitespace));
1 row inserted/updated/deleted
ij> insert into t1 values (7, xmlparse(document ' decl check ' preserve whitespace));
1 row inserted/updated/deleted
ij> update t1 set x = xmlparse(document ' document was inserted as part of an UPDATE ' preserve whitespace) where i = 1;
1 row inserted/updated/deleted
ij> update t1 set x = xmlparse(document ' document was inserted as part of an UPDATE ' preserve whitespace) where xmlexists('/update' passing by ref x);
1 row inserted/updated/deleted
ij> select i from t1 where xmlparse(document '' preserve whitespace) is not null;
I
-----
1
2
4
3
5
6
7
ij> select i from t1 where xmlparse(document '' preserve whitespace) is not null order by i;
I
-----
1
2
3
4
5
6
7
ij> prepare ps1 as 'insert into t3(i, x) values (0, XMLPARSE(document cast (? as CLOB) preserve whitespace))';
ij> execute ps1 using 'values ''caramba''';
IJ WARNING: Autocommit may close using result set
1 row inserted/updated/deleted
ij> -- "is [not] null" should work with XML.
select i from t1 where x is not null;
I
-----
1
5
6
7
ij> select i from t1 where x is null;
I
-----
2
4
3
ij> -- XML columns can't be returned in a top-level result set.
select x from t1;
ERROR 42Z71: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.
ij> select * from t1;
ERROR 42Z71: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.
ij> select xmlparse(document vc preserve whitespace) from t4;
ERROR 42Z71: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.
ij> values xmlparse(document '' preserve whitespace);
ERROR 42Z71: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.
ij> values xmlparse(document '' preserve whitespace);
ERROR 42Z71: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.
ij> -- Test XMLSERIALIZE operator.
insert into t4 values ('');
1 row inserted/updated/deleted
ij> insert into t4 values 'no good';
1 row inserted/updated/deleted
ij> -- These should fail.
select xmlserialize(x) from t1;
ERROR 42Z72: Missing SQL/XML keyword(s) 'AS' at line 2, column 22.
ij> select xmlserialize(x as) from t1;
ERROR 42X01: Syntax error: Encountered "" at line 1, column 25.
ij> select xmlserialize(x as int) from t1;
ERROR 42Z73: Invalid target type for XMLSERIALIZE: 'INTEGER'.
ij> select xmlserialize(x as varchar(20) for bit data) from t1;
ERROR 42Z73: Invalid target type for XMLSERIALIZE: 'VARCHAR () FOR BIT DATA'.
ij> select xmlserialize(y as char(10)) from t1;
ERROR 42X04: Column 'Y' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'Y' is not a column in the target table.
ij> select xmlserialize(xmlserialize(x as clob) as clob) from t1;
ERROR 42X25: The 'XMLSerialize' function is not allowed on the 'CLOB' type.
ij> values xmlserialize(' dokie ' as clob);
ERROR 42X25: The 'XMLSerialize' function is not allowed on the 'CHAR' type.
ij> -- These should succeed.
select xmlserialize(x as clob) from t1;
1
-----
document was inserted as part of an UPDATE
NULL
NULL
NULL
bass boosted.
decl check
ij> select xmlserialize(x1 as clob), xmlserialize(x2 as clob) from t5;
1 |2
-----
NULL |
ij> select xmlserialize(x as char(100)) from t1;
1
-----
document was inserted as part of an UPDATE
NULL
NULL
NULL
bass boosted.
decl check
ij> select xmlserialize(x as varchar(300)) from t1;
1
-----
document was inserted as part of an UPDATE
NULL
NULL
NULL
bass boosted.
decl check
ij> -- These should succeed at the XMLEXISTS level, but fail with
----- parse/truncation errors.
select xmlserialize(xmlparse(document vc preserve whitespace) as char(10)) from t4;
ERROR 2200M: Invalid XML DOCUMENT: Content is not allowed in prolog. SQLSTATE: XJ001: Java exception: 'Content is not allowed in prolog.: xxxFILTERED-SAX-EXCEPTIONxxx'.
ij> select xmlserialize(x as char) from t1;
ERROR 22001: A truncation error was encountered trying to shrink CHAR ' document was inserted as part of an UPDATE select xmlserialize(x as clob(10)) from t1;
1
-----
ERROR 22001: A truncation error was encountered trying to shrink CLOB ' document was inserted as part of an UPDATE select xmlserialize(x as char(1)) from t1;
ERROR 22001: A truncation error was encountered trying to shrink CHAR ' document was inserted as part of an UPDATE select length(xmlserialize(x as char(1))) from t1;
ERROR 22001: A truncation error was encountered trying to shrink CHAR ' document was inserted as part of an UPDATE select xmlserialize(x as varchar(1)) from t1;
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR ' document was inserted as part of an UPDATE select length(xmlserialize(x as varchar(1))) from t1;
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR ' document was inserted as part of an UPDATE -- These checks verify that the XMLSERIALIZE result is the correct
----- type (the type is indicated as part of the error message).
create table it (i int);
0 rows inserted/updated/deleted
ij> insert into it values (select xmlserialize(x as varchar(10)) from t1);
ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'VARCHAR'.
ij> insert into it values (select xmlserialize(x as char(10)) from t1);
ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'CHAR'.
ij> insert into it values (select xmlserialize(x as clob(10)) from t1);
ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'CLOB'.
ij> -- Test XMLPARSE/XMLSERIALIZE combinations.
----- These should fail.
select xmlserialize(xmlparse(document '' preserve whitespace) as clob) from t2;
1
-----
ERROR 2200M: Invalid XML DOCUMENT: XML document structures must start and end within the same entity. SQLSTATE: XJ001: Java exception: 'XML document structures must start and end within the same entity.: xxxFILTERED-SAX-EXCEPTIONxxx'.
ij> select xmlserialize(xmlparse(document x preserve whitespace) as char(100)) from t1;
ERROR 42X25: The 'XMLParse' function is not allowed on the 'XML' type.
ij> -- These should succeed.
select xmlserialize(xmlparse(document '' preserve whitespace) as clob) from t2;
1
-----
ij> select xmlserialize(xmlparse(document xmlserialize(x as clob) preserve whitespace) as clob) from t1;
1
-----
document was inserted as part of an UPDATE
NULL
NULL
NULL
bass boosted.
decl check
ij> values xmlserialize(xmlparse(document ' dokie ' preserve whitespace) as clob);
1
-----
dokie
ij> select i from t1 where xmlparse(document xmlserialize(x as clob) preserve whitespace) is not null order by i;
I
-----
1
5
6
7
ij> -- Test XMLEXISTS operator.
insert into t1 values (7, xmlparse(document ' this out ' preserve whitespace));
1 row inserted/updated/deleted
ij> create table t7 (i int, x1 xml, x2 xml not null);
0 rows inserted/updated/deleted
ij> insert into t7 values (1, null, xmlparse(document '' preserve whitespace));
1 row inserted/updated/deleted
ij> -- These should fail.
select i from t1 where xmlexists(x);
ERROR 42X01: Syntax error: Encountered ")" at line 2, column 35.
ij> select i from t1 where xmlexists(i);
ERROR 42X01: Syntax error: Encountered ")" at line 1, column 35.
ij> select i from t1 where xmlexists('//*');
ERROR 42X01: Syntax error: Encountered ")" at line 1, column 39.
ij> select i from t1 where xmlexists('//*' x);
ERROR 42X01: Syntax error: Encountered "x" at line 1, column 40.
ij> select i from t1 where xmlexists('//*' passing x);
ERROR 42X01: Syntax error: Encountered "x" at line 1, column 48.
ij> select i from t1 where xmlexists('//*' passing by value x);
ERROR 42Z74: XML feature not supported: 'BY VALUE'.
ij> select i from t1 where xmlexists('//*' passing by ref i);
ERROR 42Z77: Context item must have type 'XML'; 'INTEGER' is not allowed.
ij> select i from t1 where xmlexists(i passing by ref x);
ERROR 42Z75: XML query expression must be a string literal.
ij> select i from t1 where xmlexists(i passing by ref x, x);
ERROR 42Z76: Multiple XML context items are not allowed.
ij> -- These should succeed.
select i from t1 where xmlexists('//*' passing by ref x);
I
-----
1
5
6
7
7
ij> select i from t1 where xmlexists('//person' passing by ref x);
I
-----
ij> select i from t1 where xmlexists('//lets' passing by ref x);
I
-----
7
ij> select xmlexists('//lets' passing by ref x) from t1;
1
-----
0
NULL
NULL
NULL
0
0
0
1
ij> select xmlexists('//try[text()='' this out '']' passing by ref x) from t1;
1
-----
0
NULL
NULL
NULL
0
0
0
1
ij> select xmlexists('//let' passing by ref x) from t1;
1
-----
0
NULL
NULL
NULL
0
0
0
0
ij> select xmlexists('//try[text()='' this in '']' passing by ref x) from t1;
1
-----
0
NULL
NULL
NULL
0
0
0
0
ij> select i, xmlexists('//let' passing by ref x) from t1;
I |2
-----
1 |0
2 |NULL
4 |NULL
3 |NULL
5 |0
6 |0
7 |0
7 |0
ij> select i, xmlexists('//lets' passing by ref x) from t1;
I |2
-----
1 |0
2 |NULL
4 |NULL
3 |NULL
5 |0
6 |0
7 |0
7 |1
ij> values xmlexists('//let' passing by ref xmlparse(document ' try this ' preserve whitespace));
1
-----
0
ij> values xmlexists('//lets' passing by ref xmlparse(document ' try this ' preserve whitespace));
1
-----
1
ij> values xmlexists('//lets/@doit' passing by ref xmlparse(document ' try this ' preserve whitespace));
1
-----
1
ij> values xmlexists('//lets/@dot' passing by ref xmlparse(document ' try this ' preserve whitespace));
1
-----
0
ij> select xmlserialize(x1 as clob) from t5 where xmlexists('//*' passing by ref x1);
1
-----
ij> select xmlserialize(x2 as clob) from t5 where xmlexists('//*' passing by ref x2);
1
-----
ij> select xmlserialize(x1 as clob), xmlexists('//*' passing by ref xmlparse(document '' preserve whitespace)) from t5;
1 |2
-----
NULL |1
ij> select xmlserialize(x1 as clob), xmlexists('//goodboy' passing by ref xmlparse(document '' preserve whitespace)) from t5;
1 |2
-----
NULL |0
ij> select i, xmlserialize(x1 as char(10)), xmlserialize (x2 as char(10)) from t7;
I |2 |3
-----
1 |NULL |
ij> select i from t7 where xmlexists('/ok' passing by ref x1) and xmlexists('/ok' passing by ref x2);
I
-----
ij> select i from t7 where xmlexists('/ok' passing by ref x1) or xmlexists('/ok' passing by ref x2);
I
-----
1
ij> -- XMLEXISTS can be used wherever a boolean function is allowed,
----- for ex, a check constraint...
create table t6 (i int, x xml check (xmlexists('//should' passing by ref x)));
0 rows inserted/updated/deleted
ij> insert into t6 values (1, xmlparse(document '' preserve whitespace));
1 row inserted/updated/deleted
ij> insert into t6 values (1, xmlparse(document '' preserve whitespace));
ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.T6'.
ij> select xmlserialize(x as char(20)) from t6;
1
-----
ij> -- Do some namespace queries/examples.
create table t8 (i int, x xml);
0 rows inserted/updated/deleted
ij> insert into t8 values (1, xmlparse(document '' preserve whitespace));
1 row inserted/updated/deleted
ij> insert into t8 values (2, xmlparse(document '' preserve whitespace));
1 row inserted/updated/deleted
ij> insert into t8 values (3, xmlparse(document '' preserve whitespace));
1 row inserted/updated/deleted
ij> insert into t8 values (4, xmlparse(document '' preserve whitespace));
1 row inserted/updated/deleted
ij> insert into t8 values (5, xmlparse(document '' preserve whitespace));
1 row inserted/updated/deleted
ij> select xmlexists('//child::*[name()="none"]' passing by ref x) from t8;
1
-----
0
0
0
0
0
ij> select xmlexists('//child::*[name()=''hi'']' passing by ref x) from t8;
1
-----
0
0
0
0
1
ij> select xmlexists('//child::*[local-name()=''hi'']' passing by ref x) from t8;
1
-----
1
1
0
0
1
ij> select xmlexists('//child::*[local-name()=''bye'']' passing by ref x) from t8;
1
-----
0
0
1
1
0
ij> select xmlexists('//*[namespace::*[string()=''http://www.hi.there'']]' passing by ref x) from t8;
1
-----
1
1
0
1
0
ij> select xmlexists('//*[namespace::*[string()=''http://www.good.bye'']]' passing by ref x) from t8;
1
-----
0
0
1
0
0
ij> select xmlexists('//child::*[local-name()=''hi'' and namespace::*[string()=''http://www.hi.there'']]' passing by ref x) from t8;
1
-----
1
1
0
0
0
ij> select xmlexists('//child::*[local-name()=''bye'' and namespace::*[string()=''http://www.good.bye'']]' passing by ref x) from t8;
1
-----
0
0
1
0
0
ij> select xmlexists('//child::*[local-name()=''bye'' and namespace::*[string()=''http://www.hi.there'']]' passing by ref x) from t8;
1
-----
0
0
0
1
0
ij> -- Test XMLQUERY operator.
----- These should fail w/ syntax errors.
select i, xmlquery('//*') from t1;
ERROR 42X01: Syntax error: Encountered ")" at line 4, column 25.
ij> select i, xmlquery('//*' passing) from t1;
ERROR 42X01: Syntax error: Encountered ")" at line 1, column 33.
ij> select i, xmlquery('//*' passing by ref x) from t1;
ERROR 42X01: Syntax error: Encountered ")" at line 1, column 42.
ij> select i, xmlquery('//*' passing by ref x returning sequence) from t1;
ERROR 42X01: Syntax error: Encountered ")" at line 1, column 61.
ij> select i, xmlquery(passing by ref x empty on empty) from t1;
ERROR 42X01: Syntax error: Encountered "by" at line 1, column 28.
ij> select i, xmlquery(xmlquery('//*' returning sequence empty on empty) as char(75)) from t1;
ERROR 42X01: Syntax error: Encountered "returning" at line 1, column 35.
ij> -- These should fail with "not supported" errors.
select i, xmlquery('//*' passing by ref x returning sequence null on empty) from t1;
ERROR 42Z74: XML feature not supported: 'NULL ON EMPTY'.
ij> select i, xmlquery('//*' passing by ref x returning content empty on empty) from t1;
ERROR 42Z74: XML feature not supported: 'RETURNING CONTENT'.
ij> -- This should fail because XMLQUERY returns an XML value which
----- is not allowed in top-level result set.
select i, xmlquery('//*' passing by ref x empty on empty) from t1;
ERROR 42Z71: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.
ij> -- These should fail because context item must be XML.
select i, xmlquery('//*' passing by ref i empty on empty) from t1;
ERROR 42Z77: Context item must have type 'XML'; 'INTEGER' is not allowed.
ij> select i, xmlquery('//*' passing by ref 'hello' empty on empty) from t1;
ERROR 42Z77: Context item must have type 'XML'; 'CHAR' is not allowed.
ij> select i, xmlquery('//*' passing by ref cast ('hello' as clob) empty on empty) from t1;
ERROR 42Z77: Context item must have type 'XML'; 'CLOB' is not allowed.
ij> -- This should fail because the function is not recognized by Xalan.
----- The failure should be an error from Xalan saying what the problem
----- is; it should *NOT* be a NPE, which is what we were seeing before
----- DERBY-688 was completed.
select i,
xmlserialize(
xmlquery('data(//@*)' passing by ref x returning sequence empty on empty)
as char(70))
from t1;
ERROR 10000: Encountered error while evaluating XML query expression for XMLQUERY operator: Could not find function: data SQLSTATE: XJ001: Java exception: 'Could not find function: data: javax.xml.transform.TransformerException'.
ij> -- These should all succeed. Since it's Xalan that's actually doing
----- the query evaluation we don't need to test very many queries; we
----- just want to make sure we get the correct results when there is
----- an empty sequence, when the xml context is null, and when there
----- is a sequence with one or more nodes/items in it. So we just try
----- out some queries and look at the results. The selection of queries
----- is random and is not meant to be exhaustive.
select i,
xmlserialize(
xmlquery('2+2' passing by ref x returning sequence empty on empty)
as char(70))
from t1;
I |2
-----
1 |4
2 |NULL
4 |NULL
3 |NULL
5 |4
6 |4
7 |4
7 |4
ij> select i,
xmlserialize(
xmlquery('./notthere' passing by ref x returning sequence empty on empty)
as char(70))
from t1;
I |2
-----
1 |
2 |NULL
4 |NULL
3 |NULL
5 |
6 |
7 |
7 |
ij> select i,
xmlserialize(
xmlquery('//*' passing by ref x empty on empty)
as char(70))
from t1;
I |2
-----
1 | document was inserted as part of an UPDATE
2 |NULL
4 |NULL
3 |NULL
5 |
6 | bass boosted. bass
7 | decl check
7 | this out this out
ij> select i,
xmlserialize(
xmlquery('//*[text() = " bass "]' passing by ref x empty on empty)
as char(70))
from t1;
I |2
-----
1 |
2 |NULL
4 |NULL
3 |NULL
5 |
6 | bass
7 |
7 |
ij> select i,
xmlserialize(
xmlquery('//lets' passing by ref x empty on empty)
as char(70))
from t1;
I |2
-----
1 |
2 |NULL
4 |NULL
3 |NULL
5 |
6 |
7 |
7 | this out
ij> select i,
xmlserialize(
xmlquery('//text()' passing by ref x empty on empty)
as char(70))
from t1;
I |2
-----
1 | document was inserted as part of an UPDATE
2 |NULL
4 |NULL
3 |NULL
5 |
6 | bass boosted.
7 | decl check
7 | this out
ij> select i,
xmlserialize(
xmlquery('//try[text()='' this out '']' passing by ref x empty on empty)
as char(70))
from t1;
I |2
-----
1 |
2 |NULL
4 |NULL
3 |NULL
5 |
6 |
7 |
7 | this out
ij> select i,
xmlserialize(
xmlquery('//try[text()='' this in '']' passing by ref x empty on empty)
as char(70))
from t1;
I |2
-----
1 |
2 |NULL
4 |NULL
3 |NULL
5 |
6 |
7 |
7 |
ij> select i,
xmlserialize(
xmlquery('2+.//try' passing by ref x returning sequence empty on empty)
as char(70))
from t1;
I |2
-----
1 |NaN
2 |NULL
4 |NULL
3 |NULL
5 |NaN
6 |NaN
7 |NaN
7 |NaN
ij> values xmlserialize(
xmlquery('//let' passing by ref
xmlparse(document ' try this ' preserve whitespace)
empty on empty)
as char(30));
1
-----
ij> values xmlserialize(
xmlquery('//lets' passing by ref
xmlparse(document ' try this ' preserve whitespace)
empty on empty)
as char(30));
1
-----
try this
ij> -- Check insertion of XMLQUERY result into a table. Should only allow
----- results that are a sequence of exactly one Document node.
insert into t1 values (
9,
xmlparse(document 'attribute' preserve whitespace)
);
1 row inserted/updated/deleted
ij> insert into t3 values (
0,
xmlparse(document 'attribute' preserve whitespace)
);
1 row inserted/updated/deleted
ij> -- Show target tables before insertions.
select i, xmlserialize(x as char(75)) from t2;
I |2
-----
1 | work as planned
ij> select i, xmlserialize(x as char(75)) from t3;
I |2
-----
0 |caramba
0 |attribute
ij> -- These should all fail because the result of the XMLQUERY op is
----- not a valid document (it's either an empty sequence, a node that is
----- not a Document node, some undefined value, or a sequence with more
----- than one item in it).
insert into t2 (i, x) values (
20,
(select
xmlquery('./notthere' passing by ref x returning sequence empty on empty)
from t1 where i = 9
)
);
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> insert into t2 (i, x) values (
21,
(select
xmlquery('//@*' passing by ref x returning sequence empty on empty)
from t1 where i = 9
)
);
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> insert into t2 (i, x) values (
22,
(select
xmlquery('. + 2' passing by ref x returning sequence empty on empty)
from t1 where i = 9
)
);
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> insert into t2 (i, x) values (
23,
(select
xmlquery('//*' passing by ref x returning sequence empty on empty)
from t1 where i = 9
)
);
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> insert into t2 (i, x) values (
24,
(select
xmlquery('//*[//@*]' passing by ref x returning sequence empty on empty)
from t1 where i = 9
)
);
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> insert into t2 (i, x) values (
25,
(select
xmlquery('//is' passing by ref x returning sequence empty on empty)
from t1 where i = 9
)
);
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> insert into t2 (i, x) values (
26,
(select
xmlquery('//*[@*]' passing by ref x returning sequence empty on empty)
from t1 where i = 9
)
);
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> -- These should succeed.
insert into t2 (i, x) values (
27,
(select
xmlquery('.' passing by ref x returning sequence empty on empty)
from t1 where i = 9
)
);
1 row inserted/updated/deleted
ij> insert into t2 (i, x) values (
28,
(select
xmlquery('/here/..' passing by ref x returning sequence empty on empty)
from t1 where i = 9
)
);
1 row inserted/updated/deleted
ij> -- Verify results.
select i, xmlserialize(x as char(75)) from t2;
I |2
-----
1 | work as planned
27 |attribute
28 |attribute
ij> -- Next two should _both_ succeed because there's no row with i = 100
----- in t1, thus the SELECT will return null and XMLQuery operator should
----- never get executed. x will be NULL in these cases.
insert into t3 (i, x) values (
29,
(select
xmlquery('2+2' passing by ref x returning sequence empty on empty)
from t1 where i = 100
)
);
1 row inserted/updated/deleted
ij> insert into t3 (i, x) values (
30,
(select
xmlquery('.' passing by ref x returning sequence empty on empty)
from t1 where i = 100
)
);
1 row inserted/updated/deleted
ij> -- Verify results.
select i, xmlserialize(x as char(75)) from t3;
I |2
-----
0 |caramba
0 |attribute
29 |NULL
30 |NULL
ij> -- Check updates using XMLQUERY results. Should only allow results
----- that constitute a valid DOCUMENT node (i.e. that can be parsed
----- by the XMLPARSE operator).
----- These should succeed.
update t3
set x =
xmlquery('.' passing by ref
xmlparse(document '' preserve whitespace)
returning sequence empty on empty)
where i = 29;
1 row inserted/updated/deleted
ij> update t3
set x =
xmlquery('self::node()[//@height]' passing by ref
(select
xmlquery('.' passing by ref x empty on empty)
from t1
where i = 9
)
empty on empty)
where i = 30;
1 row inserted/updated/deleted
ij> -- These should fail because result of XMLQUERY isn't a DOCUMENT.
update t3
set x = xmlquery('.//*' passing by ref x empty on empty)
where i = 29;
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> update t3
set x = xmlquery('./notthere' passing by ref x empty on empty)
where i = 30;
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> update t3
set x =
xmlquery('//*[@weight]' passing by ref
(select
xmlquery('.' passing by ref x empty on empty)
from t1
where i = 9
)
empty on empty)
where i = 30;
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> update t3
set x =
xmlquery('//*/@height' passing by ref
(select
xmlquery('.' passing by ref x empty on empty)
from t1
where i = 9
)
empty on empty)
where i = 30;
ERROR 2200L: Values assigned to XML columns must be well-formed DOCUMENT nodes.
ij> -- Next two should succeed because there's no row with i = 100
----- in t3 and thus t3 should remain unchanged after these updates.
update t3
set x = xmlquery('//*' passing by ref x empty on empty)
where i = 100;
0 rows inserted/updated/deleted
ij> update t3
set x = xmlquery('4+4' passing by ref x empty on empty)
where i = 100;
0 rows inserted/updated/deleted
ij> -- Verify results.
select i, xmlserialize(x as char(75)) from t3;
I |2
-----
0 |caramba
0 |attribute
29 |
30 |attribute
ij> -- Pass results of an XMLQUERY op into another XMLQUERY op.
----- Should work so long as results of the first op constitute
----- a valid document.
----- Should fail because result of inner XMLQUERY op isn't a valid document.
select i,
xmlserialize(
xmlquery('//lets/@*' passing by ref
xmlquery('/okay/text()' passing by ref
xmlparse(document '' preserve whitespace)
empty on empty)
empty on empty)
as char(100))
from t1 where i > 5;
ERROR 2200V: Invalid context item for XMLQUERY operator; context items must be well-formed DOCUMENT nodes.
ij> select i,
xmlserialize(
xmlquery('.' passing by ref
xmlquery('//lets' passing by ref
xmlparse(document '' preserve whitespace)
empty on empty)
empty on empty)
as char(100))
from t1 where i > 5;
ERROR 2200V: Invalid context item for XMLQUERY operator; context items must be well-formed DOCUMENT nodes.
ij> select i,
xmlexists('.' passing by ref
xmlquery('/okay' passing by ref
xmlparse(document '' preserve whitespace)
empty on empty)
)
from t1 where i > 5;
ERROR 2200V: Invalid context item for XMLEXISTS operator; context items must be well-formed DOCUMENT nodes.
ij> -- Should succeed but result is empty sequence.
select i,
xmlserialize(
xmlquery('/not' passing by ref
xmlquery('.' passing by ref
xmlparse(document '' preserve whitespace)
empty on empty)
empty on empty)
as char(100))
from t1 where i > 5;
I |2
-----
6 |
7 |
7 |
9 |
ij> -- Should succeed with various results.
select i,
xmlserialize(
xmlquery('//lets' passing by ref
xmlquery('.' passing by ref
xmlparse(document '' preserve whitespace)
empty on empty)
empty on empty)
as char(100))
from t1 where i > 5;
I |2
-----
6 |
7 |
7 |
9 |
ij> select i,
xmlserialize(
xmlquery('string(//@boki)' passing by ref
xmlquery('/okay/..' passing by ref
xmlparse(document '' preserve whitespace)
empty on empty)
empty on empty)
as char(100))
from t1 where i > 5;
I |2
-----
6 |inigo
7 |inigo
7 |inigo
9 |inigo
ij> select i,
xmlserialize(
xmlquery('/half/masted/text()' passing by ref
xmlquery('.' passing by ref x empty on empty)
empty on empty)
as char(100))
from t1 where i = 6;
I |2
-----
6 | bass
ij> select i,
xmlexists('/half/masted/text()' passing by ref
xmlquery('.' passing by ref x empty on empty)
)
from t1 where i = 6;
I |2
-----
6 |1
ij> -- DERBY-1759: Serialization of attribute nodes.
----- Add a test row to t1.
insert into t1 values (10,
xmlparse(document
''
preserve whitespace
));
1 row inserted/updated/deleted
ij> -- Echo t1 rows for reference.
select i, xmlserialize(x as char(75)) from t1;
I |2
-----
1 | document was inserted as part of an UPDATE
2 |NULL
4 |NULL
3 |NULL
5 |
6 | bass boosted.
7 | decl check
7 | this out
9 |attribute
10 |
ij> -- This should fail because XML serialization dictates that
----- we throw an error if an attempt is made to serialize a
----- sequence that has one or more top-level attributes nodes.
select
xmlserialize(
xmlquery(
'//@*' passing by ref x empty on empty
)
as char(50))
from t1
where xmlexists('//@*' passing by ref x);
ERROR 2200W: XQuery serialization error: Attempted to serialize one or more top-level Attribute nodes.
ij> -- Demonstrate that Xalan "string" function only returns
----- string value of first attribute and thus cannot be
----- used to retrieve a sequence of att values.
select
xmlserialize(
xmlquery(
'string(//@*)'
passing by ref x empty on empty
)
as char(50))
from t1
where xmlexists('//@*' passing by ref x);
1
-----
4.4
1
ij> -- Xalan doesn't have a function that allows retrieval of a
----- sequence of attribute values. One can only retrieve a
----- sequence of attribute *nodes*, but since those can't be
----- serialized (because of SQL/XML rules) the user has no
----- way to get them. The following is a very (VERY) ugly
----- two-part workaround that one could use until something
----- better is available. First, get the max number of
----- attributes in the table.
select
max(
cast(
xmlserialize(
xmlquery('count(//@*)' passing by ref x empty on empty)
as char(50))
as int)
)
from t1;
1
-----
3
WARNING 01003: Null values were eliminated from the argument of a column function. :
ij> -- Then use XPath position syntax to retrieve the attributes
----- and concatenate them. We need one call to string(//@[i])
----- for every for every i between 1 and the value found in the
----- preceding query. In this case we know the max is three,
----- so use that.
select
xmlserialize(
xmlquery(
'concat(string(//@*[1]), " ",
string(//@*[2]), " ",
string(//@*[3]))'
passing by ref x empty on empty
)
as char(50))
from t1
where xmlexists('//@*' passing by ref x);
1
-----
4.4
1 two le 3 trois
ij> -- DERBY-1718
----- create trigger fails when SPS contains XML related op.
create table t9 (i int, x xml);
0 rows inserted/updated/deleted
ij> create table t10 (i int, x xml);
0 rows inserted/updated/deleted
ij> insert into t9 values (1, xmlparse(document ' john ' preserve whitespace));
1 row inserted/updated/deleted
ij> create trigger tx after insert on t9 for each statement mode db2sql
insert into t10 values (1, xmlparse(document ' jane ' preserve whitespace));
0 rows inserted/updated/deleted
ij> insert into t9 values (2, xmlparse(document ' ally ' preserve whitespace));
1 row inserted/updated/deleted
ij> select i, xmlserialize(x as varchar(20)) from t9;
I |2
-----
1 | john
2 | ally
ij> select i, xmlserialize(x as varchar(20)) from t10;
I |2
-----
1 | jane
ij> insert into t9 select * from t9;
2 rows inserted/updated/deleted
ij> select i, xmlserialize(x as varchar(20)) from t9;
I |2
-----
1 | john
2 | ally
1 | john
2 | ally
ij> select i, xmlserialize(x as varchar(20)) from t10;
I |2
-----
1 | jane
1 | jane
ij> drop trigger tx;
0 rows inserted/updated/deleted
ij> delete from t9;
4 rows inserted/updated/deleted
ij> delete from t10;
2 rows inserted/updated/deleted
ij> insert into t9 values (1, xmlparse(document ' john ' preserve whitespace));
1 row inserted/updated/deleted
ij> create trigger tx after insert on t9 for each statement mode db2sql
insert into t10 values (1, (select xmlquery('.' passing by ref x returning sequence empty on empty) from t9 where i = 1));
0 rows inserted/updated/deleted
ij> insert into t9 values (2, xmlparse(document ' ally ' preserve whitespace));
1 row inserted/updated/deleted
ij> select i, xmlserialize(x as varchar(20)) from t9;
I |2
-----
1 | john
2 | ally
ij> select i, xmlserialize(x as varchar(20)) from t10;
I |2
-----
1 | john
ij> drop trigger tx;
0 rows inserted/updated/deleted
ij> -- clean up.
drop table t0;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t4;
0 rows inserted/updated/deleted
ij> drop table t5;
0 rows inserted/updated/deleted
ij> drop table t6;
0 rows inserted/updated/deleted
ij> drop table t7;
0 rows inserted/updated/deleted
ij> drop table t8;
0 rows inserted/updated/deleted
ij> drop table t9;
0 rows inserted/updated/deleted
ij> drop table t10;
0 rows inserted/updated/deleted
ij>