MySQL – Include Default Value in LOAD XML INFILE Statement

MySQLmysql-5.5xml

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?

Best Answer

When one needs to do something more complex that is available in the syntax, it is often useful to 'stage' the data through a temp table:

Perhaps:

CREATE TEMPORARY TABLE tmp ( ... DEFAULT 'myfile' ... );
LOAD XML ...;
INSERT INTO tab1 SELECT * FROM tmp;

or:

CREATE TEMPORARY TABLE tmp LIKE tab1;
LOAD XML ...;
UPDATE tmp SET DEFAULT = 'myfile';
INSERT INTO tab1 SELECT * FROM tmp;
Related Question