douyiqi9640 2017-03-21 05:08
浏览 272

如何使用mysql LOAD XML LOCAL INFILE导入xml文件

i need help with importing xml file to a table , xml file is having millions of records .

first i was using simplexml_load_file and then put a loop to insert record one by one by that is taking a lot of time . i have used the infile for csv and that is working perfect , can anyone help , how can i do the same with xml .

i'm trying with

LOAD XML LOCAL INFILE "C:/xampp/htdocs/class/on_the_list.xml" INTO TABLE tbl_juju ROWS IDENTIFIED BY '<job>' 

running this query says "MySQL returned an empty result set (i.e. zero rows)" and no records are inserted . below is the xml format .

<?xml version="1.0" encoding="UTF-8"?>
    <rss version="2.0" xmlns:on-the-list="http://www.on-the-list.com/rss/2.0">
    <positionfeed>
    <on-the-list:import user="accounts@juju.com" site="www.juju.com" />
    <job>
    <id>335951659</id>
    <employer>The Judge Group</employer>
    <title>Java Developer</title>
    <description>Our client is currently seeking a Java Developer No C2C or 1099 Responsible for designing, developing, modifying, and evaluating programs for internal functional areas including finance, human resources, and marketing. Analyzes existing programs or formulates logic for new systems, devises logic procedures, prepares flowcharting, performs coding, and tests/debugs programs. Develops conversion and system implementation plans. Prepares and obtains approval of system and programming documentation. Recommends changes in development, maintenance, and system standards. Trains users in conversion and implementation of system. Education: 4-year degree in computer science or related field or equivalent experience Experience: 2+ years of experience in programming on enterprise or department servers or systems</description>
    <postingdate>2017-02-25</postingdate>
    <joburl>http://www.juju.com/jad/000000005k0lyj?partnerid=6facb3068fcc5ef5aa89a8cfe85e2aba&amp;exported=True&amp;hosted_timestamp=0042a345f27ac5dc911e5aa0246d6d5bb48607f376ce73ce76134eafb3ea2826</joburl>
    <location>Jacksonville, FL</location>
    <category>Software/IT</category>
    <zip />
    </job>
    <job>
    <id>335951624</id>
    <employer>Driveline Retail</employer>
    <title>Retail Merchandiser</title>
    <description>Drivelineis currently seeking motivated and career-minded candidates to join itsnational network of retail merchandisers. Weowe our continued success to the hard working, intelligent and innovativepeople who implement our retail programs every day. If you're interested injoining the team and helping our clients drive sales, then we'd love to hearfrom you. AboutDriveline: . Driveline is thelargest non-broker merchandising services agency in the country . Our clientsinclude major national retailers and manufacturers . Ourindustry-leading software makes your job easier and makes it easier than everto manage your own schedule and report hours . We offer full W-2employment, meaning no surprise tax bills from 1099 forms . We offercompetitive wages and opportunities for advancement (reimbursement is alsoavailable for travel more than 60 miles in one day) Desired experience/skills: . 1-2 yearsmerchandising or retail experience . Outstandingcustomer service skills . Ability to workboth independently and as part of a team . Ability to readstandard plan-o-grams and execute merchandise resets . Ability to liftup to 30 pounds and bend/stoop/stand for more than 45 minutes Requirements: . Reliabletransportation . Ability tomaintain a professional appearance . Computer access withprinter/Internet/email . Ability to reportcompleted work on the day of service Want to learn more or apply now? Go to www.drivelineretail.com , click on the"Apply Now" button in the top, right-hand corner of the page and complete anapplication. Our recruiters will handle the rest! We look forward to meeting you. -OR- APPLY NOW BY CLICKING BELOW ?territory_id_no=0 Desired experience/skills: . 1-2 years merchandising or retail experience . Outstanding customer service skills . Ability to work both independently and as part of a team . Ability to read standard plan-o-grams and execute merchandise resets . Ability to lift up to 30 pounds and bend/stoop/stand for more than 45 minutes</description>
    <postingdate>2017-02-24</postingdate>
    <joburl>http://www.juju.com/jad/000000005k0lxk?partnerid=6facb3068fcc5ef5aa89a8cfe85e2aba&amp;exported=True&amp;hosted_timestamp=0042a345f27ac5dc911e5aa0246d6d5bb48607f376ce73ce76134eafb3ea2826</joburl>
    <location>St. Petersburg, FL</location>
    <category>Retail</category>
    <zip />
    </job>
    </positionfeed>
    </rss>

Waiting for your help . Thanks in advance .

  • 写回答

1条回答 默认 最新

  • doucan8276 2017-03-21 08:03
    关注

    Try this

    LOAD DATA LOCAL INFILE 
        '".$path."'
        INTO TABLE 
        tbl_juju
        CHARACTER SET 'utf8'
        LINES STARTING BY '<job>' TERMINATED BY '</job>'
        (@tmp)
        SET
        id      = ExtractValue(@tmp, '//id'),
        employer = ExtractValue(@tmp, '//employer'),
        title = ExtractValue(@tmp, '//title'),
        description = ExtractValue(@tmp, '//description'),
        postingdate = ExtractValue(@tmp, '//postingdate'),
        joburl = ExtractValue(@tmp, '//joburl'),
        location_city = ExtractValue(@tmp, '//location_city'),
        location_state = ExtractValue(@tmp, '//location_state'),
        category = ExtractValue(@tmp, '//category'),
        vin = ExtractValue(@tmp, '//vin'),
        location = ExtractValue(@tmp, '//location')
    
    评论

报告相同问题?

悬赏问题

  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决