Modify column before inserting XML value to MySQL table

Question

I'm trying to import a XML file into a MySQL Table. In the XML file there is a timestamp in <CurrentTime> in the following format:

2016-01-26T09:52:19.3420655+01:00

This timstamp should go into the corresponding DATETIME CurrentTime column in my Table. So I did the following

LOAD XML INFILE 'xxx.xml'
INTO TABLE test.events
ROWS IDENTIFIED BY '<Event>'
SET CurrentTime = str_to_date(CurrentTime, '%Y-%m-%dT%H:%i:%s.%f');

But it quits with the error

Error Code: 1292. Incorrect datetime value: '2016-01-25T16:22:24.1840792+01:00' for column 'CurrentTime' at row 1

So it seems it doesn't convert the string at all. Why?

Interrestingly, declaring CurrentTime as a VARCHAR populates it with y proper DATETIME string.... — leflic
– leflic, Commented Jan 28, 2016 at 14:51

spencer7593 · Accepted Answer · 2016-01-28 14:39:09Z

1

I think that error is thrown when the string value from the file is loaded directly to the column. The error is thrown before you get to the SET clause.

Here's an abbreviated example of how to use user-defined variables to pass the value of a field down to the SET, bypassing the assignment to the column.

Note that the columns _row and account_number are populated directly from the first two fields in the file. The later fields in the file are assigned to user-defined variables (identifiers beginning with @.

The SET clause evaluates the user-defined variables, and assigns the result of the expression to the actual column in the table.

In this example, the "dates" were formatted YYYYMMDD. I used the STR_TO_DATE() function to have that string converted to a proper DATE.

I abbreviated this sample somewhat, but it demonstrates the approach of reading field values into user-defined variables.

CREATE TABLE _import_water
(`_row`             INT
,`account_number`   VARCHAR(255)
,`total_due`        DECIMAL(18,2)
,`end_date`         DATE
,`start_date`       DATE
,`ccf`              DECIMAL(18,4)
)

LOAD DATA LOCAL INFILE '//server/share$/users/me/mydir/myfile.csv'
INTO TABLE _import_water
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(_row
,account_number
,@total_due
,@end_date
,@start_date
,@ccf
)
SET `total_due`        = NULLIF(@total_due,'')
  , `end_date`         = STR_TO_DATE(@end_date,'%Y%m%d')
  , `start_date`       = STR_TO_DATE(@start_date,'%Y%m%d')
  , `ccf`              = NULLIF(@ccf,'')

Also, it doesn't look like there's any problem with your STR_TO_DATE, it seems to evaluate just fine.

testing...

SELECT STR_TO_DATE('2016-01-25T16:22:24.1840792+01:00','%Y-%m-%dT%H:%i:%s.%f') AS mydatetime

returns:

mydatetime
--------------------------
2016-01-25 16:22:24.184079

edited Jan 28, 2016 at 14:39

answered Jan 28, 2016 at 14:32

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

leflic Over a year ago

Thanks for your answer. If I use variables like in your examples, the CurrentTime column doesn't get populated at all.

leflic Over a year ago

Interrestingly, declaring CurrentTime as a VARCHAR populates it with y proper DATETIME string....

leflic Over a year ago

Furthermore, first converting the column to VARCHAR and then back to DATETIME works perfectly fine.

spencer7593 Over a year ago

@leflic: Yes. Having the column defined as VARCHAR would avoid that error because the first assignment to the column doesn't require a conversion, then the SET can can perform an operation. User-defined variables work for me. (Without seeing the statement you're running, I can't explain why CurrentTime wouldn't get populated, I'd just be making guesses. When I do that, I do have to supply a complete list of columns/variables the fields get assigned to.)

spencer7593 Over a year ago

@leflic: The error that was being thrown was because the value of the field is being assigned to the column. That assignment is done before the processing of the SET. The user-defined variable is just one approach to workaround that. (User-defined variables in the column list can also be used to "skip" fields in the file we don't need.)

|

Collectives™ on Stack Overflow

Modify column before inserting XML value to MySQL table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related