Warnings:
Warning	1105	No file name. Table will use t1.xml
#
# Testing changing table type (not in-place)
#
CREATE TABLE t1 (c INT NOT NULL, d CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=CSV HEADER=1 QUOTED=1;
Warnings:
Warning	1105	No file name. Table will use t1.csv
INSERT INTO t1 VALUES (1,'One'), (2,'Two'), (3,'Three');
SELECT * FROM t1;
c	d
1	One
2	Two
3	Three
# This would fail if the top node name is not specified.
# This is because the XML top node name defaults to the table name.
# Sure enough the temporary table name begins with '#' and is rejected by XML.
# Therefore the top node name must be specified (along with the row nodes name).
ALTER TABLE t1 TABLE_TYPE=XML TABNAME=t1 OPTION_LIST='xmlsup=libxml2,rownode=row';
SELECT * FROM t1;
c	d
1	One
2	Two
3	Three
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c` int(11) NOT NULL,
  `d` char(10) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `HEADER`=1 `QUOTED`=1 `TABLE_TYPE`=XML `TABNAME`=t1 `OPTION_LIST`='xmlsup=libxml2,rownode=row'
# Let us see the XML file
CREATE TABLE t2 (line VARCHAR(100) NOT NULL) ENGINE=CONNECT FILE_NAME='t1.xml';
Warnings:
Warning	1105	No table_type. Will be set to DOS
SELECT * FROM t2;
line
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created by the MariaDB CONNECT Storage Engine-->
<t1>
	<row>
		<TH>c</TH>
		<TH>d</TH>
	</row>
	<row>
		<c>1</c>
		<d>One</d>
	</row>
	<row>
		<c>2</c>
		<d>Two</d>
	</row>
	<row>
		<c>3</c>
		<d>Three</d>
	</row>
</t1>
# NOTE: The first (ignored) row is due to the remaining HEADER=1 option.
# Testing field option modification
ALTER TABLE t1 MODIFY d CHAR(10) NOT NULL XPATH='@', HEADER=0;
SELECT * FROM t1;
c	d
1	One
2	Two
3	Three
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c` int(11) NOT NULL,
  `d` char(10) NOT NULL `XPATH`='@'
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `QUOTED`=1 `TABLE_TYPE`=XML `TABNAME`=t1 `OPTION_LIST`='xmlsup=libxml2,rownode=row' `HEADER`=0
SELECT * FROM t2;
line
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created by the MariaDB CONNECT Storage Engine-->
<t1>
	<row d="One">
		<c>1</c>
	</row>
	<row d="Two">
		<c>2</c>
	</row>
	<row d="Three">
		<c>3</c>
	</row>
</t1>
DROP TABLE t1, t2;