Using the XCOL table type

The XCOL table is used when you have a table with a field containing a list of elements, and you want to access all these elements individually.

First, let's create a table with some data using the following queries:

CREATE TABLE anime (
  title VARCHAR(50) NOT NULL,
  characters VARCHAR(250) DEFAULT NULL
) ENGINE=CONNECT;
INSERT INTO anime VALUES 
('Fullmetal Alchemist', 'Edward Elric, Alphonse Elric, Roy Mustang, Maes Hughes, Greed, Riza Hawkeye, Alex Louis Armstrong'),
('Steins;Gate', 'Rintarou Okabe, Kurisu Makise, Mayuri Shiina, Itaru Hashida'),
('Clannad: After Story', 'Tomoya Okazaki, Nagisa Furukawa, Ushio Okazaki');

The first column contains the title of a Japanese anime, and the second column contains the names of the main characters.

Note

If you want to use a source table which doesn't use the CONNECT engine, you will need to provide more options. Please consult the documentation at https://mariadb.com/kb/en/mariadb/connect-table-types-proxy-table-type/...