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?