1

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?

  • Can this not be built into the logic of whatever creates the xml file? – Derek Downey Aug 27 '15 at 15:29
  • unfortunately not. It's an external product, over which I have no control. – IGGt Aug 27 '15 at 15:33
1

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;
| improve this answer | |
  • cheers, that's pretty much what I was thinking. I was just wondering if there was a simpler way, I guess not. – IGGt Sep 1 '15 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.