1Warnings: 2Warning 1105 No file name. Table will use t1.xml 3SET NAMES utf8; 4# 5# Testing tag values 6# 7CREATE TABLE t1 8( 9AUTHOR CHAR(50), 10TITLE CHAR(32), 11TRANSLATOR CHAR(40), 12PUBLISHER CHAR(40), 13DATEPUB INT(4) 14) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml' 15 OPTION_LIST='xmlsup=libxml2'; 16SELECT * FROM t1; 17AUTHOR Jean-Christophe Bernadac 18TITLE Construire une application XML 19TRANSLATOR NULL 20PUBLISHER Eyrolles Paris 21DATEPUB 1999 22AUTHOR William J. Pardi 23TITLE XML en Action 24TRANSLATOR James Guerin 25PUBLISHER Microsoft Press Paris 26DATEPUB 1999 27DROP TABLE t1; 28# 29# Testing that tag names are case sensitive 30# 31CREATE TABLE t1 32( 33author CHAR(50), 34TITLE CHAR(32), 35TRANSLATOR CHAR(40), 36PUBLISHER CHAR(40), 37DATEPUB INT(4) 38) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml' 39 OPTION_LIST='xmlsup=libxml2'; 40SELECT * FROM t1; 41author NULL 42TITLE Construire une application XML 43TRANSLATOR NULL 44PUBLISHER Eyrolles Paris 45DATEPUB 1999 46author NULL 47TITLE XML en Action 48TRANSLATOR James Guerin 49PUBLISHER Microsoft Press Paris 50DATEPUB 1999 51DROP TABLE t1; 52# 53# Testing attribute values 54# 55CREATE TABLE t1 ( 56ISBN CHAR(15), 57LANG CHAR(2), 58SUBJECT CHAR(32) 59) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml' 60 OPTION_LIST='Coltype=@,xmlsup=libxml2'; 61SELECT * FROM t1; 62ISBN 9782212090819 63LANG fr 64SUBJECT applications 65ISBN 9782840825685 66LANG fr 67SUBJECT applications 68DROP TABLE t1; 69# 70# Testing that attribute names are case sensitive 71# 72CREATE TABLE t1 ( 73isbn CHAR(15), 74LANG CHAR(2), 75SUBJECT CHAR(32) 76) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml' 77 OPTION_LIST='Coltype=@,xmlsup=libxml2'; 78SELECT * FROM t1; 79isbn NULL 80LANG fr 81SUBJECT applications 82isbn NULL 83LANG fr 84SUBJECT applications 85DROP TABLE t1; 86# 87# Testing mixed tag and attribute values 88# 89CREATE TABLE t1 ( 90ISBN CHAR(15) XPATH='@', 91LANG CHAR(2) XPATH='@', 92SUBJECT CHAR(32) XPATH='@', 93AUTHOR CHAR(50), 94TITLE CHAR(32), 95TRANSLATOR CHAR(40), 96PUBLISHER CHAR(40), 97DATEPUB INT(4) 98) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml' 99 TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK' 100 OPTION_LIST='xmlsup=libxml2'; 101SELECT * FROM t1; 102ISBN 9782212090819 103LANG fr 104SUBJECT applications 105AUTHOR Jean-Christophe Bernadac 106TITLE Construire une application XML 107TRANSLATOR NULL 108PUBLISHER Eyrolles Paris 109DATEPUB 1999 110ISBN 9782840825685 111LANG fr 112SUBJECT applications 113AUTHOR William J. Pardi 114TITLE XML en Action 115TRANSLATOR James Guerin 116PUBLISHER Microsoft Press Paris 117DATEPUB 1999 118DROP TABLE t1; 119# 120# Testing INSERT on mixed tag and attribute values 121# 122CREATE TABLE t1 ( 123ISBN CHAR(15) XPATH='@', 124LANG CHAR(2) XPATH='@', 125SUBJECT CHAR(32) XPATH='@', 126AUTHOR CHAR(50), 127TITLE CHAR(32), 128TRANSLATOR CHAR(40), 129PUBLISHER CHAR(40), 130DATEPUB INT(4) 131) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample2.xml' 132 TABNAME='BIBLIO' 133 OPTION_LIST='rownode=BOOK,xmlsup=libxml2'; 134INSERT INTO t1 (ISBN, LANG, SUBJECT, AUTHOR, TITLE, PUBLISHEr, DATEPUB) 135VALUES('9782212090529','fr','général','Alain Michard', 136'XML, Langage et Applications','Eyrolles Paris',1998); 137SELECT * FROM t1; 138ISBN 9782212090819 139LANG fr 140SUBJECT applications 141AUTHOR Jean-Christophe Bernadac 142TITLE Construire une application XML 143TRANSLATOR NULL 144PUBLISHER Eyrolles Paris 145DATEPUB 1999 146ISBN 9782840825685 147LANG fr 148SUBJECT applications 149AUTHOR William J. Pardi 150TITLE XML en Action 151TRANSLATOR James Guerin 152PUBLISHER Microsoft Press Paris 153DATEPUB 1999 154ISBN 9782212090529 155LANG fr 156SUBJECT général 157AUTHOR Alain Michard 158TITLE XML, Langage et Applications 159TRANSLATOR NULL 160PUBLISHER Eyrolles Paris 161DATEPUB 1998 162SELECT LOAD_FILE('MYSQLD_DATADIR/test/xsample2.xml') AS xml; 163xml <?xml version="1.0" encoding="UTF-8"?> 164<BIBLIO SUBJECT="XML"> 165 <BOOK ISBN="9782212090819" LANG="fr" SUBJECT="applications"> 166 <AUTHOR> 167 <FIRSTNAME>Jean-Christophe</FIRSTNAME> 168 <LASTNAME>Bernadac</LASTNAME> 169 </AUTHOR> 170 <AUTHOR> 171 <FIRSTNAME>François</FIRSTNAME> 172 <LASTNAME>Knab</LASTNAME> 173 </AUTHOR> 174 <TITLE>Construire une application XML</TITLE> 175 <PUBLISHER> 176 <NAME>Eyrolles</NAME> 177 <PLACE>Paris</PLACE> 178 </PUBLISHER> 179 <DATEPUB>1999</DATEPUB> 180 </BOOK> 181 <BOOK ISBN="9782840825685" LANG="fr" SUBJECT="applications"> 182 <AUTHOR> 183 <FIRSTNAME>William J.</FIRSTNAME> 184 <LASTNAME>Pardi</LASTNAME> 185 </AUTHOR> 186 <TRANSLATOR PREFIX="adapté de l'anglais par"> 187 <FIRSTNAME>James</FIRSTNAME> 188 <LASTNAME>Guerin</LASTNAME> 189 </TRANSLATOR> 190 <TITLE>XML en Action</TITLE> 191 <PUBLISHER> 192 <NAME>Microsoft Press</NAME> 193 <PLACE>Paris</PLACE> 194 </PUBLISHER> 195 <DATEPUB>1999</DATEPUB> 196 </BOOK> 197 <BOOK ISBN="9782212090529" LANG="fr" SUBJECT="général"> 198 <AUTHOR>Alain Michard</AUTHOR> 199 <TITLE>XML, Langage et Applications</TITLE> 200 <PUBLISHER>Eyrolles Paris</PUBLISHER> 201 <DATEPUB>1998</DATEPUB> 202 </BOOK> 203</BIBLIO> 204 205DROP TABLE t1; 206# 207# Testing XPath 208# 209CREATE TABLE t1 ( 210isbn CHAR(15) XPATH='@ISBN', 211language CHAR(2) XPATH='@LANG', 212subject CHAR(32) XPATH='@SUBJECT', 213authorfn CHAR(20) XPATH='AUTHOR/FIRSTNAME', 214authorln CHAR(20) XPATH='AUTHOR/LASTNAME', 215title CHAR(32) XPATH='TITLE', 216translated CHAR(32) XPATH='TRANSLATOR/@PREFIX', 217tranfn CHAR(20) XPATH='TRANSLATOR/FIRSTNAME', 218tranln CHAR(20) XPATH='TRANSLATOR/LASTNAME', 219publisher CHAR(20) XPATH='PUBLISHER/NAME', 220location CHAR(20) XPATH='PUBLISHER/PLACE', 221year INT(4) XPATH='DATEPUB' 222) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml' 223 TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK,skipnull=1,xmlsup=libxml2'; 224SELECT * FROM t1; 225isbn 9782212090819 226language fr 227subject applications 228authorfn Jean-Christophe 229authorln Bernadac 230title Construire une application XML 231translated NULL 232tranfn NULL 233tranln NULL 234publisher Eyrolles 235location Paris 236year 1999 237isbn 9782840825685 238language fr 239subject applications 240authorfn William J. 241authorln Pardi 242title XML en Action 243translated adapté de l'anglais par 244tranfn James 245tranln Guerin 246publisher Microsoft Press 247location Paris 248year 1999 249SELECT isbn, title, translated, tranfn, tranln, location FROM t1 250WHERE translated <> ''; 251isbn 9782840825685 252title XML en Action 253translated adapté de l'anglais par 254tranfn James 255tranln Guerin 256location Paris 257DROP TABLE t1; 258# 259# Testing that XPath is case sensitive 260# 261CREATE TABLE t1 262( 263isbn CHAR(15) XPATH='@isbn' 264) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml' 265 TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK,skipnull=1,xmlsup=libxml2'; 266SELECT * FROM t1; 267isbn NULL 268isbn NULL 269DROP TABLE t1; 270# 271# Testing character sets 272# 273CREATE TABLE t1 274( 275c CHAR(16) 276) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml' 277 OPTION_LIST='xmlsup=libxml2' 278 DATA_CHARSET=latin1; 279ERROR HY000: DATA_CHARSET='latin1' is not supported for TABLE_TYPE=XML 280CREATE TABLE t1 281( 282c CHAR(16) 283) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml' 284 OPTION_LIST='xmlsup=libxml2' 285 DATA_CHARSET=utf8; 286SHOW CREATE TABLE t1; 287Table t1 288Create Table CREATE TABLE `t1` ( 289 `c` char(16) DEFAULT NULL 290) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=XML `FILE_NAME`='latin1.xml' `OPTION_LIST`='xmlsup=libxml2' `DATA_CHARSET`=utf8 291SELECT c, HEX(c) FROM t1; 292c ÁÂÃÄÅÆÇ 293HEX(c) C1C2C3C4C5C6C7 294DROP TABLE t1; 295CREATE TABLE t1 296( 297c CHAR(16) 298) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml' 299 OPTION_LIST='xmlsup=libxml2'; 300SELECT c, HEX(c) FROM t1; 301c ÁÂÃÄÅÆÇ 302HEX(c) C1C2C3C4C5C6C7 303DROP TABLE t1; 304CREATE TABLE t1 305( 306c CHAR(16) CHARACTER SET utf8 307) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml' 308 OPTION_LIST='xmlsup=libxml2'; 309SELECT c, HEX(c) FROM t1; 310c ÁÂÃÄÅÆÇ 311HEX(c) C381C382C383C384C385C386C387 312DROP TABLE t1; 313# 314# Conversion from latin1 to cp1251 produces a warning. 315# Question marks are returned. 316# 317CREATE TABLE t1 318( 319c CHAR(16) CHARACTER SET cp1251 320) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='latin1.xml' 321 OPTION_LIST='xmlsup=libxml2'; 322SELECT c, HEX(c) FROM t1; 323c ??????? 324HEX(c) 3F3F3F3F3F3F3F 325Warnings: 326Level Warning 327Code 1366 328Message Incorrect string value: '\xC3\x81\xC3\x82\xC3\x83...' for column `test`.`t1`.`c` at row 1 329Level Warning 330Code 1105 331Message Out of range value ÁÂÃÄÅÆÇ for column 'c' at row 1 332DROP TABLE t1; 333# 334# Testing Cyrillic 335# 336# 337# Testing that the underlying file is created with a proper Encoding 338# 339CREATE TABLE t1 (node VARCHAR(50)) 340CHARACTER SET latin1 341ENGINE=connect TABLE_TYPE=xml FILE_NAME='t1.xml' 342 OPTION_LIST='xmlsup=libxml2,rownode=line,encoding=utf-8'; 343INSERT INTO t1 VALUES (_latin1 0xC0C1C2C3); 344SELECT node, hex(node) FROM t1; 345node ÀÁÂÃ 346hex(node) C0C1C2C3 347DROP TABLE t1; 348SET @a=LOAD_FILE('MYSQLD_DATADIR/test/t1.xml'); 349SELECT LEFT(@a,38); 350LEFT(@a,38) <?xml version="1.0" encoding="utf-8"?> 351SELECT HEX(EXTRACTVALUE(@a,'/t1/line/node')); 352HEX(EXTRACTVALUE(@a,'/t1/line/node')) C380C381C382C383 353CREATE TABLE t1 (node VARCHAR(50)) 354CHARACTER SET latin1 355ENGINE=connect TABLE_TYPE=xml FILE_NAME='t1.xml' 356 OPTION_LIST='xmlsup=libxml2,rownode=line,encoding=iso-8859-1'; 357INSERT INTO t1 VALUES (_latin1 0xC0C1C2C3); 358SELECT node, hex(node) FROM t1; 359node ÀÁÂÃ 360hex(node) C0C1C2C3 361DROP TABLE t1; 362SET @a=LOAD_FILE('MYSQLD_DATADIR/test/t1.xml'); 363SELECT LEFT(@a,43); 364LEFT(@a,43) <?xml version="1.0" encoding="iso-8859-1"?> 365SELECT HEX(EXTRACTVALUE(@a,'/t1/line/node')); 366HEX(EXTRACTVALUE(@a,'/t1/line/node')) C0C1C2C3 367# 368# Testing XML entities 369# 370CREATE TABLE t1 (node VARCHAR(50)) 371CHARACTER SET utf8 372ENGINE=connect TABLE_TYPE=xml FILE_NAME='t1.xml' 373 OPTION_LIST='xmlsup=libxml2,rownode=line,encoding=iso-8859-1'; 374INSERT INTO t1 VALUES (_latin1 0xC0C1C2C3); 375INSERT INTO t1 VALUES (_cp1251 0xC0C1C2C3); 376INSERT INTO t1 VALUES ('&<>"\''); 377SELECT node, hex(node) FROM t1; 378node ÀÁÂÃ 379hex(node) C380C381C382C383 380node АБВГ 381hex(node) D090D091D092D093 382node &<>"' 383hex(node) 263C3E2227 384DROP TABLE t1; 385SET @a=LOAD_FILE('MYSQLD_DATADIR/test/t1.xml'); 386SELECT CAST(@a AS CHAR CHARACTER SET latin1); 387CAST(@a AS CHAR CHARACTER SET latin1) <?xml version="1.0" encoding="iso-8859-1"?> 388<!-- Created by the MariaDB CONNECT Storage Engine--> 389<t1> 390 <line> 391 <node>ÀÁÂÃ</node> 392 </line> 393 <line> 394 <node>АБВГ</node> 395 </line> 396 <line> 397 <node>&<>"'</node> 398 </line> 399</t1> 400 401