The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

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 Answered by Ian Gilfillan in this comment.

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.