How import xml files with mysql LOAD XML LOCAL INFILE

mysql, xml

Solution

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

Problem

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

Original source