How import xml files with mysql LOAD XML LOCAL INFILE

Question

I have a xml file like this:

test.xml

<?xml version="1.0" encoding="utf-8" ?>
<plugin name="tree">
    <title>Test</title>
    <description>some description</description>
    <files>
        <file>test.tmp</file>
    </files>
    <install><![CDATA[
        global $test;
    ]]></install>
    <hooks>
        <hook name="hookname"><![CDATA[
            global $local;
        ]]></hook>
    </hooks>

    <phrases>
        <phrase key="category"><![CDATA[Show categories]]></phrase>
    </phrases>
</plugin>

and i like to import it into a MySQL Table like 'mytable'

CREATE TABLE mytable (plugin varchar(255),title varchar(255),description varchar(255), file varchar(255),install varchar(255),hook varchar(255),phrase varchar(255));

I used below command

LOAD XML LOCAL INFILE 'test.xml' 
INTO TABLE mytable(plugin,title,description,file,install,hook,phrase);

it runs successfully but with 0 rows!

The query has been successfully implemented, 0 rows have been affected.

Thank you

Rahul · Accepted Answer · 2014-05-29 11:51:29Z

14

Include this line ROWS IDENTIFIED BY '<plugin>'. with that your query should look like

LOAD XML LOCAL INFILE "D:\\test.xml"
INTO TABLE mytable
ROWS IDENTIFIED BY '<plugin>';

Looks like your XML file formation is not correct and so even though 1 row gets inserted; all the values doesn't gets extracted (remains NULL).

Do little changes as below

Create table structure

CREATE TABLE mytable (
plugin_name varchar(255),
title varchar(255),
description varchar(255), 
`file` varchar(255),
`install` varchar(255),
hook varchar(255),
phrase varchar(255));

Change your XML file

<?xml version="1.0" encoding="utf-8" ?>
<plugin plugin_name="tree">
    <title>Test</title>
    <description>some description</description>
         <file>test.tmp</file>
    <install>![CDATA[
        global $test;
    ]]</install>
        <hook name="hookname">![CDATA[
            global $local;
        ]]</hook>
        <phrase key="category">![CDATA[Show categories]]</phrase>
</plugin>

Now if you use

LOAD XML LOCAL INFILE "D:\\test.xml"
INTO TABLE mytable
ROWS IDENTIFIED BY '<plugin>';

All data gets extracted fine

enter image description here

edited May 29, 2014 at 11:51

answered May 29, 2014 at 11:33

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lcm Over a year ago

what if you cant change the xml file?

tgunr Over a year ago

The xml file HAS to be in one of the three forms described, see dev.mysql.com/doc/refman/5.5/en/load-xml.html

tgunr Over a year ago

Also, SHOW WARNINGS executed right after the attempted import may shed light on problems encountered.

John Over a year ago

As a word of caution on latest MariaDB: Exporting XML took about 5 minutes. Importing again takes 48 hours. The xml implementation is garbage.

Adeel Raza Azeemi · Accepted Answer · 2020-10-08 06:39:48Z

0

Beside the answer given above; I just wanted to add that the database fields character casing must be same as that of the XML field casing. Or else load XML wouldn't be able to import that specific column(s).

answered Oct 8, 2020 at 6:39

Adeel Raza Azeemi

8399 silver badges16 bronze badges

Collectives™ on Stack Overflow

How import xml files with mysql LOAD XML LOCAL INFILE

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related