Nested XML to MySQL tables using php

Question

I am new to XML and php and this might sound like a very basic question. I have this XML file which is like.

  <testsuites>
  <testsuite name="test1" errors="0" skipped="0" tests="19" failures="0" time="2093">
  <properties>
     <property name="script" value="smoketest" />
     <property name="script_start_time" value="14042" />
     <property name="dut_type" value="SSR10" />
     <property name="block" value="20PGIGE" />
     <property name="dut_image" value="IPOS-15.1.27.100.98-Release" />
  </properties>
  <testcase classname="SSR/smoketest" name="TC200: Verify PPA3LP " time="4">
  </testcase>

I need to enter all the values in a mysql table. Created the database with all the fields. The problem is with the nesting structure inside properties and testcase. I need all those values too. can someone guide me to a tutorial which explains how to do it using php. Thanks.

You could just append tag names together, like properties_property_script, etc. — developerwjk, Jul 11 '14 at 22:18

score 2 · Accepted Answer · edited May 23 '17 at 12:23

As the XML contains hierarchical data (elements with attribute and child elements again with attributes) but your database table is a flat structure (row with columns) you need to transform the data.

Data in the XML Document Tree:

  \- testsuite
  |    |- name="test1"
  |    \- properties
  |         |- property
  |         |     \- name="script" value="smoketest"
 ...       ...

Data in the Testsuites Table:

Testsuites

  +-------+-----------+- ...
  | name  | script    | ...
  +-------+-----------+- ...
  | test1 | smoketest | ...
  +-------+-----------+- ...

Even though the way the structure is organized is different, both represent a list of fields, so even the XML is organized hierarchical, the data represented there-in can be represented as a list. This is a pre-condition to transform it.

As both are some kind of a list, you can deal with it in a loop. You solve the transformation by iterating over elements in the XML document, namely you iterate over testsuites if one such element represents all the data of a single row.

Then you obtain the data of each such testsuite element and store it into a flat structure like an array. You then use this array to insert a row.

This mapping is done most easily with XML by making use of XPath in combination with a context-node. The context-node here is the testsuite element you've got in each iteration.

...
'name'   => 'string(./@name)',
...
'script' => 'string(./properties/property[@name="script"]/@value)',
...

Such a mapping allows you an easy configuration in a flexible way to get all needed data from each iteration. This principle had been outlined earlier in How can I extract structured text from an HTML list in PHP?. Here is a more simplified example with the XML chunk of yours:

$buffer = <<<XML
<testsuites>
  <testsuite name="test1" errors="0" skipped="0" tests="19" failures="0" time="2093">
  <!-- ... all the data you've got ... -->
  </testsuite>
</testsuites>
XML;

$mapping = [
    'name'   => 'string(./@name)',
    'script' => 'string(./properties/property[@name="script"]/@value)',
];

$doc = new DOMDocument();
$doc->loadXML($buffer);
$xpath = new DOMXPath($doc);

$testSuites = $xpath->evaluate('/*/testsuite');

foreach ($testSuites as $testSuite) {
    $row = [];
    foreach ($mapping as $name => $expression) {
        $row[$name] = $xpath->evaluate($expression, $testSuite);
    }
    print_r($row);
}

This does output with the exemplary data from your question:

Array
(
    [name] => test1
    [script] => smoketest
)

The mapping shows how you can access attribute values and also attribute values based on another attribute value of a specific element. So these two mappings cover all your cases. In general, this is just xpath and if you search for specific "how to do this or that in xpath" on site, you can also find more complex things, like dealing with XML namespaces which is not covered in my example.

I guess you know how to insert data into the database, as now with the array (or even better perhaps the mapping already), this should be easy as you turned the tree-structure into a flat structure.

Nested XML to MySQL tables using php

1 Answers1

Linked