Import XML (CDATA)

Question

Good afternoon !

I have researching in all web, and i haven´t found anything about it. That´s weird, maybe it´s a personal problem. Because anyone has problem like me.

So, i made a simple command in mysql to make that job

LOAD XML LOCAL INFILE 
    'person.xml'
INTO TABLE 
    person
ROWS IDENTIFIED BY 
    '<person>'

The file person.xml

<?xml version="1.0" encoding="ISO-8859-1"?>
<list>
  <person>
      <field name="person_id">5</field>
      <field name="fname"><![CDATA[Ronström]]></field>
      <field name="lname">Ulin</field>
  </person>
  <person>
      <field name="person_id">6</field>
      <field name="fname"><![CDATA[Mikael]]></field>
      <field name="lname">Sköld</field>
  </person>
</list>

The INSERT is done without problems, the point is that not make the insert the values inside of the tag CDATA, it´s returning NULL. There is some way to strip that tag, to return only values ?!

Possible duplicate of (also unanswered) http://stackoverflow.com/questions/13107639/problems-with-mysql-load-xml-infile and http://stackoverflow.com/questions/12882983/importing-xml-to-mysql-errors — Charlie Gorichanaz, Apr 20 '13 at 18:32
Charlie... there is no solution in the both topics... I need some solution to that simple detail. By the way, thanks to help me. — Ronaldo, Apr 20 '13 at 19:18
I know you'd like a solution... I did some searching but haven't come across this before. :-/ — Charlie Gorichanaz, Apr 20 '13 at 22:01

score 2 · Answer 1 · answered Oct 10 '13 at 01:15

I couldn't get this to work using LOAD XML INFILE either - it appears to eat the CDATA contents.

I did get it working using regular old LOAD DATA INFILE though. Given your example XML file and this table:

CREATE TABLE `people` (
  `person_id` int(11) NOT NULL PRIMARY KEY,
  `fname` text,
  `lname` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

running this will import the data from the file into the table:

LOAD DATA INFILE 
    '/tmp/person.xml'
INTO TABLE 
    people
CHARACTER SET 'utf8'
LINES STARTING BY '<person>' TERMINATED BY '</person>'
(@person)
SET
  person_id = ExtractValue(@person, '//field[@name="person_id"]'),
  fname     = ExtractValue(@person, '//field[@name="fname"]'),
  lname     = ExtractValue(@person, '//field[@name="lname"]')
;

This works by telling LOAD DATA INFILE that each <person>...</person> is a logical 'line', which it stores in the local variable @person. We then pass this to the ExtractValue() function as an XML fragment, select the values from it that we want using the appropriate XPath expression and store the result in the appropriate column.

score 0 · Answer 2 · answered Jul 27 '16 at 16:52

Ronaldo, This may or may not pertain to your question (and you probably have already found a solution) but for anyone else:

If you're using php to load the xml file:

You need to load the xml file with the LIBXML_NOCDATA flag set. That will pull it in properly.

So if you had before: $xml = simplexml_load_file('some/file/path.xml');

You want this:

$xml = simplexml_load_file('some/file/path.xml',null,LIBXML_NOCDATA);

Import XML (CDATA)

2 Answers2