Load XML data into MySQL table

[Harm_Millaard]

I have numerous XML files, one for each record, with a structure like this (only a small part actually, each file is around 170 KB):

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<speccydata version="1.20.446" formatversion="2.0" languageID="0" time="20130323T222711" localtime="20130323T232711+0100">

    <mainsection title="Summary">

        <section title="Operating System" id="1">

            <entry value="Microsoft Windows 8 Enterprise 64-bit" />

        </section>

        <section title="CPU" id="2">

            <entry title="Intel Core i7 3930K @ 3.20GHz" value="32 °C" />

            <entry title="Sandy Bridge-E 32nm Technology" value="" />

        </section>

        <section title="RAM" id="3">

            <entry value="64,0 GB DDR3 @ 1091MHz (11-11-11-31)" />

        </section>

        <section title="Motherboard" id="4">

            <entry title="ASUSTeK COMPUTER INC. P9X79 WS (LGA2011)" value="25 °C" />

        </section>

        <section title="Graphics" id="5">

            <entry title="SyncMaster (1920x1080@60Hz)" value="" />

            <entry title="SyncMaster (1920x1080@60Hz)" value="" />

            <entry title="24W_LCD_TV (1920x1080@50Hz)" value="" />

            <entry title="Microsoft Basic Render Driver" value="" />

            <entry title="NVIDIA GeForce GTX 680" value="" />

        </section>

        <section title="Hard Drives" id="6">

            <entry title="238GB Corsair Performance Pro (SSD)" value="" />

            <entry title="238GB Corsair Performance Pro (SSD)" value="" />

            <entry title="932GB Seagate ST1000NM0011 (SATA)" value="24 °C" />

            <entry title="16764GB Areca ARC-1882-VOL#000 SCSI Disk Device (RAID)" value="" />

        </section>

        <section title="Optical Drives" id="7">

            <entry title="HL-DT-ST BD-RE BH10LS38" value="" />

            <entry title="HL-DT-ST BD-RE BH10LS38" value="" />

        </section>

        <section title="Audio" id="8">

            <entry title="Realtek High Definition Audio" value="" />

        </section>

    </mainsection>

    <mainsection title="Operating System" id="1">

        <entry>

            <entry title="Model" value="MS Windows 8" />

            <entry title="Edition" value="Enterprise" />

            <entry title="Architecture" value="64-bit" />

            <entry title="Service Pack" value="" />

        </entry>

I have a table in MySQL, where each record contains the name of the .XML file in a field.

I need to open the .XML file and load relevant data into a specific field for record 1, then repeat for record 2, until all records are filled with the data from the various .XML files. As an example from the above .XML code, I want to extract the BOLD information and get it into fields called:

os, cpu, ram, mobo, graphics, hard_drives, etc.

How to do that?

[David_Powers]

Not a trivial task, but one that could be done by writing a script that queries the database to get the XML filename and the record's primary key.

You can then use SimpleXML to open the XML file, extract the values that you need, and store them in variables. Use the variables and primary key to build a SQL query to update the record with the data you have extracted.