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

Question

我需要有关将 xml 文件导入表的帮助，xml 文件有数百万条记录。

首先，我使用的是 simplexml_load_file，然后放置一个循环来逐条插入记录，这会花费大量时间。我已经使用了 csv 的 infile，它工作得很好，任何人都可以帮忙，我怎样才能对 xml 做同样的事情。

我正在尝试

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

运行这个查询说“MySQL 返回了一个空结果集(即零行)”并且没有插入任何记录。以下是 xml 格式。

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

等待您的帮助。提前致谢。

Answer 1

试试这个

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