How do I load data from XML where date is in %W %e, %Y format?

Question

I can load the XML file but the data columns show as NULL. I want the column to be a date column but I can't figure out how to format the two date columns. If I add:

SET date1= STR_TO_DATE(@ddate1,'%M %e, %Y'), SET date2= STR_TO_DATE(@date2,'%M %e, %Y'); to the end of my load data, it fails on the second set. If I remove the second set it doesn't fail but date1 is still NULL. There are 25 columns in total and the date columns are the last columns so if there is a way to do it w/o listing out each column, that would be preferred.

Note: My workaround was to change the column to varchar and then did an update on the table to get the date format right.

Answer 1

there isn't, load data works so that you have to list them all if you want to manipulate one column.

the are some shortcuts though.

In Mysql Workbench, you can right click on a table send to Sql editor ->< Insert Statement and you can copy all the column names at once, and change the the last column.

other tools have similar posibilities

Answer 2

LOAD XML LOCAL INFILE 'path/to/xml/file.xml'
INTO TABLE your_table
ROWS IDENTIFIED BY '<row>'
(
  @date_column
  -- other columns
)
SET date_column = STR_TO_DATE(@date_column, '%W %e, %Y');