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>