How to convert datetime format while importing xml
I have an xml file.
A sample: <?xml version="1.0" standalone="yes"?> <testData> <item> <Column1>5</Column1> <Column2>3/1/2020 6:04:46 AM</Column2> <Column3>3/1/2020 6:04:46 AM</Column3> <Column4>1/1/0001</Column4> </item> <item> <Column1>6</Column1> <Column2>3/1/2020 6:04:46 AM</Column2> <Column3>3/1/2020 6:04:46 AM</Column3> <Column4>1/1/0001</Column4> </item> </testData>
Now, I want to import it to mariadb, so I created a table like below.
CREATE TABLE `test` ( `Column1` INT(11) NULL DEFAULT NULL, `Column2` DATETIME NULL DEFAULT NULL, `Column3` TIMESTAMP NULL DEFAULT NULL, `Column4` DATE NULL DEFAULT NULL ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB
I tried to import XML data using below command on console.
LOAD XML INFILE "C:/temp/test.XML" INTO TABLE dmlicensedata.test ROWS IDENTIFIED BY '<item>';
But I am getting this error.
ERROR 1292 (22007): Incorrect datetime value: '3/1/2020 6:04:46 AM' for column `dmlicensedata`.`test`.`Column2` at row 1
This is because of the date format I guess. Because the datetime format should be 2020-08-12 16:23:09, but mine is in 3/1/2020 6:04:46 AM The XML file contains 1000s of records, is there any option to convert this before importing?
Answer
You can use SET COLUMN and the STR_TO_DATE function, for example:
LOAD XML INFILE "C:/temp/test.XML" INTO TABLE dmlicensedata.test ROWS IDENTIFIED BY '<item>' (column1, @var1, ...) SET column2 = STR_TO_DATE(@var1,'%d/%m/%Y %r'), column3=... ;
Note if your original data uses American dates, the day and month will be reversed.