1drop table if exists t1, t2; 2create table t1 (a int, b varchar(64)); 3-- Load a static XML file 4load xml infile '../../std_data/loadxml.dat' into table t1 5rows identified by '<row>'; 6select * from t1 order by a; 7a b 81 b1 92 b2 103 b3 1111 b11 12111 b111 13112 b112 & < > " ' &unknown; -- check entities 14212 b212 15213 b213 16214 b214 17215 b215 18216 &bb b; 19delete from t1; 20-- Load a static XML file with 'IGNORE num ROWS' 21load xml infile '../../std_data/loadxml.dat' into table t1 22rows identified by '<row>' ignore 4 rows; 23select * from t1 order by a; 24a b 25111 b111 26112 b112 & < > " ' &unknown; -- check entities 27212 b212 28213 b213 29214 b214 30215 b215 31216 &bb b; 32-- Check 'mysqldump --xml' + 'LOAD XML' round trip 33delete from t1; 34load xml infile 'MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1 rows identified by '<row>';; 35select * from t1 order by a; 36a b 37111 b111 38112 b112 & < > " ' &unknown; -- check entities 39212 b212 40213 b213 41214 b214 42215 b215 43216 &bb b; 44--Check that default row tag is '<row> 45delete from t1; 46load xml infile 'MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1;; 47select * from t1 order by a; 48a b 49111 b111 50112 b112 & < > " ' &unknown; -- check entities 51212 b212 52213 b213 53214 b214 54215 b215 55216 &bb b; 56-- Check that 'xml' is not a keyword 57select 1 as xml; 58xml 591 60connect addconroot, localhost, root,,; 61connection addconroot; 62create table t2(fl text); 63LOAD XML LOCAL INFILE "MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" INTO TABLE t2 ROWS IDENTIFIED BY '<person>';; 64connection default; 65drop table t1; 66drop table t2; 67create table t1 ( 68id int(11) not null, 69text text, 70primary key (id) 71) engine=MyISAM default charset=latin1; 72load xml infile '../../std_data/loadxml2.dat' into table t1; 73select * from t1; 74id text 751 line1 76line2 77line3 78drop table t1; 79# 80# Bug#51571 load xml infile causes server crash 81# 82CREATE TABLE t1 (a text, b text); 83LOAD XML INFILE '../../std_data/loadxml.dat' INTO TABLE t1 84ROWS IDENTIFIED BY '<row>' (a,@b) SET b=concat('!',@b); 85SELECT * FROM t1 ORDER BY a; 86a b 871 ! b1 8811 ! b11 89111 !b111 90112 !b112 & < > " ' &unknown; -- check entities 912 ! b2 92212 !b212 93213 !b213 94214 !b214 95215 !b215 96216 !&bb b; 973 ! b3 98DROP TABLE t1; 99# 100# Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS 101# 102CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 VARCHAR(3), col4 VARCHAR(4)); 103LOAD XML INFILE '../../std_data/bug16171518_1.dat' INTO TABLE t1; 104SELECT * FROM t1 ORDER BY col1, col2, col3, col4; 105col1 col2 col3 col4 1060bc def ghi jkl 1071no NULL pqr stu 1082BC DEF GHI JKL 1093NO NULL PQR STU 1104bc def ghi jkl 1115no pqr stu vwx 1126BC DEF NULL JKL 1137NO PQR STU VWX 1148bc def ghi NULL 1159kl NULL mno pqr 116ABC DEF NULL JKL 117MNO NULL STU VWX 118DROP TABLE t1; 119CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER); 120LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t1; 121SELECT * FROM t1 ORDER BY col1, col2, col3; 122col1 col2 col3 123ABC DEF NULL 124GHI NULL 123 125DROP TABLE t1; 126# 127# MDEV-12696 Crash with LOAD XML and non-updatable VIEW column 128# 129CREATE TABLE t1 (c1 TEXT); 130CREATE VIEW v1 AS SELECT CONCAT(c1,'') AS c1, NULL AS c2 FROM t1; 131LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c1); 132ERROR HY000: Column 'c1' is not updatable 133LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c2); 134ERROR HY000: Column 'c2' is not updatable 135DROP VIEW v1; 136DROP TABLE t1; 137# 138# MDEV-14628 Wrong autoinc value assigned by LOAD XML in the NO_AUTO_VALUE_ON_ZERO mode 139# 140SET sql_mode=NO_AUTO_VALUE_ON_ZERO; 141CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT); 142LOAD XML INFILE '../../std_data/loaddata/mdev14628a.xml' INTO TABLE t1 ROWS IDENTIFIED BY '<row>'; 143SELECT * FROM t1 ORDER BY b; 144a b 1451 bbb1 1462 bbb2 147DROP TABLE t1; 148SET sql_mode=DEFAULT; 149SET sql_mode=''; 150CREATE TABLE t1 (id INT, g GEOMETRY NOT NULL); 151LOAD XML INFILE '../../std_data/loaddata/mdev14628b.xml' INTO TABLE t1 ROWS IDENTIFIED BY '<row>'; 152ERROR 22004: Column set to default value; NULL supplied to NOT NULL column 'g' at row 1 153SELECT * FROM t1; 154id g 155DROP TABLE t1; 156SET sql_mode=DEFAULT; 157