如何使用mysql导入XML文件LOAD XML LOCAL INFILE [英] How import xml files with mysql LOAD XML LOCAL INFILE

问题描述

我有一个这样的xml文件:

I have a xml file like this:

test.xml

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>

并且我喜欢将其导入到MySQL表中，例如"mytable"

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);

它成功运行但有0行！

查询已成功实现，已记录0行 受影响.

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

谢谢

推荐答案

包括此行ROWS IDENTIFIED BY '<plugin>'.这样您的查询应类似于

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>';

看起来您的XML文件格式不正确，因此即使插入了1行也是如此；不会提取所有值(仍为NULL).

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).

做如下小改动

创建表结构

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));

更改您的XML文件

<?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>

现在，如果您使用

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

所有数据都可以很好地提取

All data gets extracted fine

这篇关于如何使用mysql导入XML文件LOAD XML LOCAL INFILE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！