How can parse xml response in to variable and store in to table using php and mysql?

Question

How can I parse an XML response into variable and store in to table?

XML response is fetched with the following script:

<?php
require_once 'db.php';
define( 'HOST', 'https://gateway.autodns.com');
define( 'XML_FILE', 'test.xml' );
$xml = implode( "", file(XML_FILE) );
header( 'Content-Type: text/xml' );
echo requestCurl( $xml );
function requestCurl( $data )
{
    $ch = curl_init( HOST );
    curl_setopt ( $ch, CURLOPT_POSTFIELDS, $data );
    curl_setopt ( $ch, CURLOPT_RETURNTRANSFER, 1 );
    curl_setopt ( $ch, CURLOPT_SSL_VERIFYPEER, FALSE );
    if( !$data = curl_exec( $ch )) {
        echo 'Curl execution error.', curl_error( $ch ) ."\n"; return FALSE;
    }
    curl_close( $ch );
    return $data;
}

$api_insert = $mysqli->query("INSERT INTO api name, created, payable VALUES ('', '', '')");
?>

echo requestCurl( $xml ); prints XML content shown below. I need to take <name>, <payable> and <created> element values for each domain and store them into a table.

XML

<response>
<result>
<data>
<summary>2</summary>
<domain>
<name>facebook.bayern</name>
<payable>2017-05-06 13:34:46</payable>
<domainsafe>false</domainsafe>
<dnssec>false</dnssec>
<owner>
<user>dhabi</user>
<context>4</context>
</owner>
<created>2015-05-06 13:34:46</created>
<updated>2016-12-20 11:29:10</updated>
</domain>
<domain>
<name>google.com</name>
<payable>2017-04-08 22:04:04</payable>
<domainsafe>false</domainsafe>
<dnssec>false</dnssec>
<owner>
<user>dhabi</user>
<context>4</context>
</owner>
<created>2016-04-08 22:04:05</created>
<updated>2016-12-20 17:44:39</updated>
</domain>
</data>
<status>
<code>S0105</code>
<text>Domaindaten wurden erfolgreich ermittelt.</text>
<type>success</type>
</status>
</result>
<stid>20161221-app4-40522</stid>
</response>

Mayank Pandeyz · Accepted Answer · 2016-12-21 05:13:07Z

2

PHP has a nifty method called simplexml_load_string and what it does is it loads an XML structured string into an easy to use object. Let’s say you have a string variable called $xmlResponse that has the following properly formatted XML in it:

$xmlResponse

<?xml version='1.0'?>
<business>
    <company>Code Blog</company>
    <owner>Nic Raboy</owner>
    <employees>
        <employee>
            <firstname>Nic</firstname>
            <lastname>Raboy</lastname>
        </employee>
        <employee>
            <firstname>Maria</firstname>
            <lastname>Campos</lastname>
        </employee>
    </employees>
</business>

Doing the following in PHP will give us a very nice object to work with:

$xml = simplexml_load_string($xmlResponse);

Reference

answered Dec 21, 2016 at 5:13

Mayank Pandeyz

26.3k4 gold badges43 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ruslan Osmanov · Accepted Answer · 2016-12-21 07:09:13Z

0

Using DOM extension:

// $xml = 'XML content';
$doc = new DOMDocument;
$doc->loadXML($xml);
$xpath = new DOMXPath($doc);
$expr = '/response/result//domain';
$domains = $xpath->query($expr);

foreach ($domains as $d) {
  $props = [];
  foreach ($d->childNodes as $child) {
    $props[$child->nodeName] = $child->nodeValue;
  }

  if (
    isset($props['name']) &&
    isset($props['payable']) &&
    isset($props['created'])
  )
  {
    printf(
      "Name: %s\nPayable: %s\nCreated: %s\n\n",
      $props['name'],
      $props['payable'],
      $props['created']
    );
  }
}

The XPath expression selects all (//) domain nodes under /response/result/. DOMXPath::query returns a Traversable list of nodes into $domains variable.

The next loop fetches DOMElement items from $domains, and the inner loop collects the child nodes into $props array. If all elements (name, payable, and created) are collected, printf prints them to the standard output.

Output

Name: facebook.bayern
Payable: 2017-05-06 13:34:46
Created: 2015-05-06 13:34:46

Name: google.com
Payable: 2017-04-08 22:04:04
Created: 2016-04-08 22:04:05

Instead of fetching all child nodes, you may fetch only specific elements using an extra call to XPath:

$children = $xpath->query('*[self::name or self::payable or self::created]', $d);
if ($children->length != 3) {
  continue;
}

Note, the domain element is passed as a context node for the relative XPath expression.

In order to store the values into database using MySQLi replace the printf call with the following:

$sql = sprintf(
  'INSERT INTO `api` (name, created, payable) VALUES ("%s", "%s", "%s")',
  $mysqli->real_escape_string($props['name']),
  $mysqli->real_escape_string($props['payable']),
  $mysqli->real_escape_string($props['created'])
);
$result = $mysqli->query($sql);

edited Dec 21, 2016 at 7:09

answered Dec 21, 2016 at 6:19

Ruslan Osmanov

21.8k8 gold badges52 silver badges62 bronze badges

3 Comments

Sarath TS Over a year ago

Thanks. When I print

"printf(       "Name: %s\nPayable: %s\nCreated: %s\n\n",       $props['name'],       $props['payable'],       $props['created']     );"

I am getting error XML Parsing Error: no root element found Location: https://localhost/auto_dns_api/api.php Line Number 1, Column 1: Do you know why it is showing like this.

Sarath TS Over a year ago

$xml = requestCurl( $xml ) correct? If I am defining like this getting error like

XML Parsing Error: syntax error Location: https://localhost/auto_dns_api/api.php Line Number 1, Column 1:Name: facebook.bayern

Ruslan Osmanov Over a year ago

@samsam, this is a working code. You need to set $xml variable to a string with XML downloaded from the remote host

Arup Garai · Accepted Answer · 2016-12-21 05:14:09Z

0

Make XML to array using XML parse example below

$simple = "<para><note>simple note</note></para>";
$p = xml_parser_create();
xml_parse_into_struct($p, $simple, $vals, $index);
xml_parser_free($p);
echo "Index array\n";
print_r($index);
echo "\nVals array\n";
print_r($vals);

answered Dec 21, 2016 at 5:14

Arup Garai

1491 silver badge7 bronze badges

1 Comment

Sarath TS Over a year ago

I tried this method but getting some error.

$simple = requestCurl( $xml ); $p = xml_parser_create(); xml_parse_into_struct($p, $simple, $vals, $index); xml_parser_free($p); echo "Index array\n"; print_r($index); echo "\nVals array\n"; print_r($vals);

Error is XML Parsing Error: syntax error Location: https://localhost/auto_dns_api/api.php Line Number 1, Column 1:Index array

Collectives™ on Stack Overflow

How can parse xml response in to variable and store in to table using php and mysql?

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related