MySQL Forums
Forum List  »  Newbie

MySQL - include a default value in LOAD XML INFILE statement
Posted by: IGG t
Date: August 28, 2015 09:23AM

I have the follwing table:

CREATE TABLE `tab1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`DEFAULT` VARCHAR(25) DEFAULT NULL,
`ITEM` varchar(25) DEFAULT NULL,
`TIMESTAMP` TIMESTAMP DEFAULT NULL,
`NAME` varchar(25) DEFAULT NULL,
`SURNAME` varchar(45) DEFAULT NULL,
`LOADED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
I then have an XML file containing multiple entries such as:

<RECORD
ITEM="item1"
TIMESTAMP="2015-08-26 10:04:40"
NAME="Bob"
SURNAME="SMITH"
/>
I use MySQL to import the data with:

LOAD XML LOCAL INFILE 'c:/myFile.xml'
INTO TABLE `test`.`tab1`
ROWS IDENTIFIED BY '<RECORD>';
This works OK. However I need to specify a Default value for the DEFAULT field. But it needs to be specified at the time I load the file, and will be different for each file. (for the sake of argument let's use the filename, myfile.xml)

How can I add a default value for this row during the XML Import?

I looked at using something like:

LOAD XML LOCAL INFILE 'c:/myFile.xml'
INTO TABLE `test`.`tab1` (@default="myfile.xml")
ROWS IDENTIFIED BY '<RECORD>';
SET DEFAULT=@default;
But it didn't like that. Is there a way round this?

Options: ReplyQuote


Subject
Written By
Posted
MySQL - include a default value in LOAD XML INFILE statement
August 28, 2015 09:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.