📄 xml_general.out
字号:
ij> -- 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/deletedij> 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 '<hmm>' preserve whitespace) as clob) from t2;1 -----ERROR 2200L: XMLPARSE operand is not an XML document; see next exception for details. SQLSTATE: XJ001: Java exception: 'XML document structures must start and end within the same entity.: org.xml.sax.SAXParseException'.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 '<hmm/>' preserve whitespace) as clob) from t2;1 -----<hmm/> ij> select xmlserialize(xmlparse(document xmlserialize(x as clob) preserve whitespace) as clob) from t1;1 -----<update2> document was inserted as part of an UPDATE </update2> NULL NULL NULL <hmm/> <half> <masted> bass </masted> boosted. </half> ij> values xmlserialize(xmlparse(document '<okay> dokie </okay>' preserve whitespace) as clob);1 -----<okay> dokie </okay> ij> select i from t1 where xmlparse(document xmlserialize(x as clob) preserve whitespace) is not null order by i;I -----1 5 6 ij> -- Test XMLEXISTS operator.insert into t1 values (7, xmlparse(document '<lets> <try> this out </try> </lets>' preserve whitespace));1 row inserted/updated/deletedij> create table t7 (i int, x1 xml, x2 xml not null);0 rows inserted/updated/deletedij> insert into t7 values (1, null, xmlparse(document '<ok/>' preserve whitespace));1 row inserted/updated/deletedij> -- These should fail.select i from t1 where xmlexists(x);ERROR 42X01: Syntax error: Encountered ")" at line 1, 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 ref x);ERROR X0X18: XML feature not supported: 'BY REF'.ij> select i from t1 where xmlexists('//*' passing by value i);ERROR 42Y95: The 'XMLExists' operator with a left operand type of 'CHAR' and a right operand type of 'INTEGER' is not supported.ij> -- These should succeed.select i from t1 where xmlexists('//*' passing by value x);I -----1 5 6 7 ij> select i from t1 where xmlexists('//person' passing by value x);I -----ij> select i from t1 where xmlexists('//lets' passing by value x);I -----7 ij> select xmlexists('//lets' passing by value x) from t1;1 -----0 NULL NULL NULL 0 0 1 ij> select xmlexists('//try[text()='' this out '']' passing by value x) from t1;1 -----0 NULL NULL NULL 0 0 1 ij> select xmlexists('//let' passing by value x) from t1;1 -----0 NULL NULL NULL 0 0 0 ij> select xmlexists('//try[text()='' this in '']' passing by value x) from t1;1 -----0 NULL NULL NULL 0 0 0 ij> select i, xmlexists('//let' passing by value x) from t1;I |2 -----1 |0 2 |NULL 4 |NULL 3 |NULL 5 |0 6 |0 7 |0 ij> select i, xmlexists('//lets' passing by value x) from t1;I |2 -----1 |0 2 |NULL 4 |NULL 3 |NULL 5 |0 6 |0 7 |1 ij> values xmlexists('//let' passing by value xmlparse(document '<lets> try this </lets>' preserve whitespace));1 -----0 ij> values xmlexists('//lets' passing by value xmlparse(document '<lets> try this </lets>' preserve whitespace));1 -----1 ij> select xmlserialize(x1 as clob) from t5 where xmlexists('//*' passing by value x1);1 -----ij> select xmlserialize(x2 as clob) from t5 where xmlexists('//*' passing by value x2);1 -----<notnull/> ij> select xmlserialize(x1 as clob), xmlexists('//*' passing by value xmlparse(document '<badboy/>' preserve whitespace)) from t5;1 |2 -----NULL |1 ij> select xmlserialize(x1 as clob), xmlexists('//goodboy' passing by value xmlparse(document '<badboy/>' 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 |<ok/> ij> select i from t7 where xmlexists('/ok' passing by value x1) and xmlexists('/ok' passing by value x2);I -----ij> select i from t7 where xmlexists('/ok' passing by value x1) or xmlexists('/ok' passing by value 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 value x)));0 rows inserted/updated/deletedij> insert into t6 values (1, xmlparse(document '<should/>' preserve whitespace));1 row inserted/updated/deletedij> insert into t6 values (1, xmlparse(document '<shouldnt/>' 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 -----<should/> ij> -- Do some namespace queries/examples.create table t8 (i int, x xml);0 rows inserted/updated/deletedij> insert into t8 values (1, xmlparse(document '<a:hi xmlns:a="http://www.hi.there"/>' preserve whitespace));1 row inserted/updated/deletedij> insert into t8 values (2, xmlparse(document '<b:hi xmlns:b="http://www.hi.there"/>' preserve whitespace));1 row inserted/updated/deletedij> insert into t8 values (3, xmlparse(document '<a:bye xmlns:a="http://www.good.bye"/>' preserve whitespace));1 row inserted/updated/deletedij> insert into t8 values (4, xmlparse(document '<b:bye xmlns:b="http://www.hi.there"/>' preserve whitespace));1 row inserted/updated/deletedij> insert into t8 values (5, xmlparse(document '<hi/>' preserve whitespace));1 row inserted/updated/deletedij> select xmlexists('//child::*[name()="none"]' passing by value x) from t8;1 -----0 0 0 0 0 ij> select xmlexists('//child::*[name()=''hi'']' passing by value x) from t8;1 -----0 0 0 0 1 ij> select xmlexists('//child::*[local-name()=''hi'']' passing by value x) from t8;1 -----1 1 0 0 1 ij> select xmlexists('//child::*[local-name()=''bye'']' passing by value x) from t8;1 -----0 0 1 1 0 ij> select xmlexists('//*[namespace::*[string()=''http://www.hi.there'']]' passing by value x) from t8;1 -----1 1 0 1 0 ij> select xmlexists('//*[namespace::*[string()=''http://www.good.bye'']]' passing by value 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 value 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 value 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 value x) from t8;1 -----0 0 0 1 0 ij> -- clean up.drop table t0;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t3;0 rows inserted/updated/deletedij> drop table t4;0 rows inserted/updated/deletedij> drop table t5;0 rows inserted/updated/deletedij> drop table t6;0 rows inserted/updated/deletedij> drop table t7;0 rows inserted/updated/deletedij> drop table t8;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -