Grrr... You are forcing me to learn about XML and MySQL.
Jon's suggestion of s/.../.../ assumes that you have an editor (such as vi) that uses that syntax for the "substitute" command. It sounds like you don't.
Your XML follows the first format in
http://dev.mysql.com/doc/refman/5.5/en/load-xml.html
Column names as attributes and column values as attribute values:
<row column1="value1" column2="value2" .../>
Here is code that loaded your data successfully:
CREATE TABLE f624508 (
MMSI INT UNSIGNED NOT NULL,
TIME INT UNSIGNED NOT NULL,
LONGITUDE INT SIGNED NOT NULL,
LATITUDE INT SIGNED NOT NULL,
COG SMALLINT UNSIGNED NULL,
SOG TINYINT UNSIGNED NOT NULL,
HEADING SMALLINT UNSIGNED NOT NULL,
NAVSTAT TINYINT UNSIGNED NOT NULL,
IMO INT UNSIGNED NOT NULL,
NAME VARCHAR(33) NULL,
CALLSIGN VARCHAR(33) NULL,
TYPE SMALLINT UNSIGNED NOT NULL,
A SMALLINT UNSIGNED NOT NULL,
B SMALLINT UNSIGNED NOT NULL,
C SMALLINT UNSIGNED NOT NULL,
D SMALLINT UNSIGNED NOT NULL,
DRAUGHT SMALLINT UNSIGNED NOT NULL,
DEST VARCHAR(33) NULL,
ETA INT UNSIGNED NULL
-- PRIMARY KEY (??)
) ENGINE=InnoDB;
LOAD XML LOCAL INFILE 'C:/htdocs/misc/tryxml.xml'
INTO TABLE f624508
ROWS IDENTIFIED BY '<vessel>'
(MMSI, TIME, LONGITUDE, LATITUDE, COG, SOG, HEADING, NAVSTAT, IMO, NAME, CALLSIGN, TYPE, A, B, C, D, DRAUGHT, DEST, ETA);
SHOW WARNINGS LIMIT 22;
Notes:
* Your table should have a PRIMARY KEY; I did not know what to make it.
* The SHOW WARNINGS is handy if things don't load correctly.
* I picked datatypes based on the data I saw.
* LATITUDE and LONGITUDE should probably be divided by 1000000, but the "SET" technique probably fails for XML input, at least case #1. So, you can do that in a separate step.
* NOT NULL is preferred, however, you are missing some values, and they were treated as NULL, so I made those fields NULL.
* TIME should probably be put into a TIMESTAMP field, but that gets a bit tricky.
* You can turn NULL back into "" by doing IFNULL(COG, '') in the SELECT, for example:
mysql> select DEST, IFNULL(DEST, ''), COG, SOG, TIME from f624508 limit 19;
+----------------+------------------+------+-----+------------+
| DEST | IFNULL(DEST, '') | COG | SOG | TIME |
+----------------+------------------+------+-----+------------+
| LIVERPOOL | LIVERPOOL | 3526 | 0 | 1416928823 |
| GRAVENDEEL | GRAVENDEEL | 1643 | 0 | 1416928855 |
| ROTTERDAM | ROTTERDAM | 3060 | 0 | 1416928866 |
| MOERDIJK NL | MOERDIJK NL | 2670 | 0 | 1416928862 |
| MAASTRICHT | MAASTRICHT | 3260 | 35 | 1416928830 |
| MIAMI | MIAMI | 1980 | 0 | 1416928828 |
| TOWING SCHELD | TOWING SCHELD | 640 | 0 | 1416928854 |
| HUI ZHOU | HUI ZHOU | 1649 | 84 | 1416928851 |
| NEW YORK | NEW YORK | 2599 | 1 | 1416928823 |
| BRIXHAM | BRIXHAM | 2267 | 1 | 1416928868 |
| NULL | | 3600 | 0 | 1416928859 | <-- for example
| QINHUANGDAO | QINHUANGDAO | 2478 | 0 | 1416928394 |
| CANAL D ALSACE | CANAL D ALSACE | 0 | 0 | 1416928285 |
| ===STATIONARY | ===STATIONARY | 2950 | 0 | 1416928197 |
| HARBOUR TUG | HARBOUR TUG | 1475 | 0 | 1416928869 |
| NULL | | 2888 | 0 | 1416928862 | <-- for example
| NULL | | 2957 | 0 | 1416928802 | <-- for example
| BALARD OIL | BALARD OIL | 184 | 0 | 1416928865 |
| UA ODS>UA NIK | UA ODS>UA NIK | 3582 | 0 | 1416928855 |
+----------------+------------------+------+-----+------------+
19 rows in set (0.00 sec)