XML Read by XPath

PriteshP23 · August 12, 2013

I would like to get Type, Id and Height values in database table. It is not working. There is no error message.

XML File:

<?xml version="1.0" encoding="utf-8"?>
<Physical>
<Catalog>
<EquipmentSpec>
<Reference>030_17</Reference>
<Type>Duplexeur</Type>
<Characteristic><CharacteristicName>Tilt</CharacteristicName>
<CharacteristicValue>0</CharacteristicValue></Characteristic>
</EquipmentSpec>
<EquipmentSpec>
<Reference>030_19</Reference>
<Type>Node</Type>
<Characteristic><CharacteristicName>Tilt</CharacteristicName>
<CharacteristicValue>0</CharacteristicValue></Characteristic>
</EquipmentSpec>
</Catalog>
<Installed>
<Equipment>
<Id>26</Id><Reference>MAT_4</Reference>
<Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
</Equipment>
<Equipment>
<Id>27</Id><Reference>MAT_6890</Reference>
<Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
</Equipment>
</Installed>
</Physical>

CODE:

foreach ($xml->xpath('Physical') as $spec)
{
    foreach ($spec->xpath('//EquipmentSpec') as $sub)
    {
        $Type = $sub->Type;
    }
    foreach ($spec->xpath('//Equipment') as $eq)
    {
        $Id = $eq->Id;
        foreach ($eq->Characteristic as $c)
        {
            if ($c->CharacteristicName == 'Height')
            {
                $Height = $c->CharacteristicValue;
            }
        }    
    }

    $sql = "INSERT INTO machines (`Type`,`Id`,`Height`) VALUES ('".$Type."','".$Id."','".$Height."')";
    $req = new requete($site->db, $sql);
    echo $sql;

}

sKunKbad · August 12, 2013

I've only used SimpleXML, and wonder why you are using xpath? If you just need a quick and easy way to get that value, why not consider this:

<?php
$xml = simplexml_load_string( $xml );
$json = json_encode( $xml );
$arr = json_decode( $json, TRUE );

This will give you a simple array to work with, instead of XML which tends to take more effort to work with.

Edited August 12, 2013 by sKunKbad

requinix · August 12, 2013

For the Type and Id you definitely don't need XPath. For the Height... yeah, XPath.

foreach ($xml->Catalog->xpath("EquipmentSpec") as $n => $equipmentspec) { // xpath for the numeric key
	$equipment = $xml->Installed->Equipment[$n];

	$Type = (string)$equipmentspec->Type;
	$Id = (int)$equipment->Id;
	$Height = (int)current($equipment->xpath("Characteristic[CharacteristicName='Height']/CharacteristicValue")); // a search

	$sql = "INSERT INTO machines (`Type`, `Id`, `Height`) VALUES ('{$Type}', {$Id}, {$Height})";
	$req = new requete($site->db, $sql);
	echo $sql;
}

Never understood why people feel like arrays are easier than SimpleXML...

sKunKbad · August 12, 2013

Never understood why people feel like arrays are easier than SimpleXML...

I work with a company that uses an XML API that has some pretty massive XML data requests/responses. I know all things SimpleXML, and many scores of hours of experience with it. I've parsed this XML in various ways, and experimented with generating the XML in various ways. It doesn't really matter when you're working with some small amount of XML data, but when you've got a few hundred nodes to parse (or generate), using SimpleXML is more time consuming than just converting the whole thing to an array (or automagically creating the XML from an array). It really just boils down to less typing. I'm not saying SimpleXML is hard, but less typing is easier, especially when dealing with big XML. So yes, in real life using an array may be easier. When your fingers hurt and have numbness from your countless hours of typing, you may agree with me.

PriteshP23 · August 13, 2013

I've only used SimpleXML, and wonder why you are using xpath?

I am using DOM parser. I believe it's more easy to read complex and big size XML files. I have 850Mb XML file and i should use XPath to get values.

For the Type and Id you definitely don't need XPath. For the Height... yeah, XPath.

Here it is:

if (file_exists("test.xml"))
{
    $xml = simplexml_load_file("test.xml");
}
else
{
    echo "error file";
}
$dom = new DomDocument;
$dom->preserveWhiteSpace = FALSE;
$dom->validateOnParse = true;
$dom->strictErrorChecking = FALSE ;
@$dom->load($xml);

$xpath = new DOMXpath($dom);
$mtest = fopen("mtest", "w+");

foreach ($xml->Catalog->xpath("EquipmentSpec") as $n => $equipmentspec)
{ // xpath for the numeric key
    $equipment = $xml->Installed->Equipment[$n];

    $Type = (string)$equipmentspec->Type;
    $Id = (int)$equipment->Id;
    $Height = (int)current($equipment->xpath("Characteristic[CharacteristicName='Height']/CharacteristicValue")); // a search
    
    echo $Type;
    echo $Id;
    echo $Height;
   
    $sql = "INSERT INTO machines (`Type`, `Id`, `Height`) VALUES ('{$Type}', {$Id}, {$Height})";
    $req = new requete($site->db, $sql);
    echo $sql;
}
 fclose($mtest);

Thank you all for your input.

It's not giving expected output. $:-\$

PriteshP23 · August 13, 2013

Any idea ?? :facewall: :php_tags:

Thanks in advanced for your help.

requinix · August 13, 2013

It's not giving expected output. $:-\$

That is not helpful for us. We can't see what's going on. You have to describe what's happening.

PriteshP23 · August 14, 2013

You have to describe what's happening.

I believe this is not working. It has error but there is no message.

$sql = "INSERT INTO machines (`Type`, `Id`, `Height`) VALUES ('{$Type}', {$Id}, {$Height})";

Well, I have 850 Mb XML file and i just would like to present small part of it once again:

<?xml version="1.0" encoding="utf-8"?>
<Physical>
    <Catalog>
        <EquipmentSpec>
            <Reference>MAT_4</Reference>
            <Type>Duplexeur</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>0</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_6890</Reference>
            <Type>Node</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>2</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_50</Reference>
            <Type>Single</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>5</CharacteristicValue></Characteristic>
        </EquipmentSpec>
    </Catalog>
    <Installed>
        <Equipment>
            <Id>26</Id><Reference>MAT_4</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
        </Equipment>
        <Equipment>
            <Id>27</Id><Reference>MAT_6890</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>140</CharacteristicValue></Characteristic>
        </Equipment>
        <Equipment>
            <Id>28</Id><Reference>MAT_50</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
        </Equipment>
    </Installed>
</Physical>

In addition, i believe $xml->Catalog->xpath is not good path.

foreach ($xml->Catalog->xpath("EquipmentSpec") as $n => $equipmentspec) 
{
     // xpath for the numeric key
    //....
}

EXPECTED output:

echo $Type;
  $sql = "INSERT INTO LTE_noria_antennes (`Type`,`Id`,`Height`) VALUES ('{$Type}', '{$Id}', '{$Height}')";
  $req = new requete($site->db, $sql);

It should insert in database table like this:

RefId**    Type          Id    Height
1       Duplexeur    26     160

2              Node       27     140

3         Single      28    180

// **RefId is column auto_incriment to count rows.

I hope i am clear to you.

PriteshP23 · August 14, 2013

Objective is to get RESPECTIVE "Type" from Catalaog which has same Reference from Installed.

PriteshP23 · August 14, 2013

That is not helpful for us. We can't see what's going on.

@requinix:

print_r($equipmentspec);
print_r($equipment);

/* Not printing any values...  */

Any idea to solve this problem ??

requinix · August 14, 2013

The only thing I got out of those three posts was that the XML file is 850 MB and

print_r($equipmentspec);
print_r($equipment);

/* Not printing any values...  */

that the script isn't outputting anything.

The XML may be too large to load into memory. Open up your php.ini and change

error_reporting = -1
display_errors = on
memory_limit = 0

then restart the web server (if you're running this script from a web page) and try again. Any errors?

PriteshP23 · August 15, 2013

The only thing I got out of those three posts was that the XML file is 850 MB

There is no SIZE problem at all.

Objective is to get RESPECTIVE "Type" from Catalaog which has SAME Reference from Installed. So that is the main TASK to get the result.

I tried this but not working:

foreach($xml as $key=>$val) {
    foreach($val as $key2=>$val2) {
        $Type = $val2->Type;
        foreach($val2 as $key3=>$val3) {
            if($val3->CharacteristicName == 'Height') {
                $Height = $val3->CharacteristicValue;
            }
        }
    }
}

For this XML, it should give TWO Type, not three as there are ONLY TWO are SAME.

<?xml version="1.0" encoding="utf-8"?>
<Physical>
    <Catalog>
        <EquipmentSpec>
            <Reference>MAT_478788</Reference>
            <Type>Duplexeur</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>0</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_6890</Reference>
            <Type>Node</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>2</CharacteristicValue></Characteristic>
        </EquipmentSpec>
        <EquipmentSpec>
            <Reference>MAT_50</Reference>
            <Type>Single</Type>
            <Characteristic><CharacteristicName>Tilt</CharacteristicName>
            <CharacteristicValue>5</CharacteristicValue></Characteristic>
        </EquipmentSpec>
    </Catalog>
    <Installed>
        <Equipment>
            <Id>26</Id><Reference>MAT_4</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
        </Equipment>
        <Equipment>
            <Id>27</Id><Reference>MAT_6890</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>140</CharacteristicValue></Characteristic>
        </Equipment>
        <Equipment>
            <Id>28</Id><Reference>MAT_50</Reference>
            <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
        </Equipment>
    </Installed>
</Physical>

I hope i am clear.

Thanks in adv for quick response,

us2rn4m2 · August 15, 2013

replace foreach ($xml->xpath('Physical') as $spec)

by foreach ($xml->xpath('//Catalog') as $spec)

PriteshP23 · August 16, 2013

I did. It is not inserting in table and it should insert TWO rows as TWO Type are matching.

XML

    <?xml version="1.0" encoding="utf-8"?>
    <Physical>
       <Catalog>
          <EquipmentSpec>
             <Reference>MAT_48985</Reference>
             <Type>Duplexeur</Type>
             <Characteristic><CharacteristicName>Tilt</CharacteristicName>
             <CharacteristicValue>0</CharacteristicValue></Characteristic>
          </EquipmentSpec>
          <EquipmentSpec>
             <Reference>MAT_6890</Reference>
             <Type>Node</Type>
             <Characteristic><CharacteristicName>Tilt</CharacteristicName>
             <CharacteristicValue>2</CharacteristicValue></Characteristic>
          </EquipmentSpec>
          <EquipmentSpec>
             <Reference>MAT_50</Reference>
             <Type>Single</Type>
             <Characteristic><CharacteristicName>Tilt</CharacteristicName>
             <CharacteristicValue>5</CharacteristicValue></Characteristic>
          </EquipmentSpec>
       </Catalog>
       <Installed>
          <Equipment>
             <Id>26</Id><Reference>MAT_4</Reference>
             <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
          </Equipment>
          <Equipment>
             <Id>27</Id><Reference>MAT_6890</Reference>
             <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>140</CharacteristicValue></Characteristic>
          </Equipment>
          <Equipment>
             <Id>28</Id><Reference>MAT_50</Reference>
             <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
          </Equipment>
       </Installed>
    </Physical>

CODE:

    foreach ($xml->xpath('Catalog') as $spec)
    {
        foreach ($spec->xpath('//EquipmentSpec') as $sub)
        {
            $Type = $sub->Type;
          echo $Type;
        }
        foreach ($spec->xpath('//Equipment') as $eq)
        {
            $Id = $eq->Id;
          echo $Id;
            foreach ($eq->Characteristic as $c)
            {
                if ($c->CharacteristicName == 'Azimut')
                {
                    $Azimut = $c->CharacteristicValue;
                }
            }   
        }

        $sql = "INSERT INTO machines (`Type`,`Id`,`Azimut`) VALUES ('".$Type."','".$Id."','".$Height."')";
        $req = new requete($site->db, $sql);
        echo $sql;
       if (!$req)
       {
          $message  = 'Invalid query: ' . mysql_error() . "\n";
          $message .= 'Whole query: ' . $sql;
          die($message);
       }
    }

OUTPUT

    DuplexeurNodeSingle262728INSERT INTO LTE_noria_antennes (`Type`,`Id`,`Azimut`) VALUES ('Single','28','180')

I know there is little mistake but i couldn't solve it out. Thank you all for input.

Barand · August 16, 2013

My code, which searched for a matching reference in the installed section, finds 3 matches, not 2

foreach ($xml->xpath("//Catalog/EquipmentSpec") as $n => $equipmentspec) {
    $ref = (string)$equipmentspec->Reference;
    $type = (string)$equipmentspec->Type;
    $installed = $xml->xpath("//Installed/Equipment[Reference='$ref']");
    if ($installed) {
        foreach ($installed as $inst) {
            $id = (string)$inst->Id;
            $heightObj = $inst->xpath("Characteristic[CharacteristicName='Height']");
            $height = (string)$heightObj[0]->CharacteristicValue;
            echo "$ref | $type | $id | $height<br>";
            // insertion to db here
        }
    }
}

Output

MAT_4    | Duplexeur | 26 | 160
MAT_6890 | Node      | 27 | 140
MAT_50   | Single    | 28 | 180

PriteshP23 · August 16, 2013

My code, which searched for a matching reference in the installed section, finds 3 matches, not 2

It seems good. Well, it should give TWO.

If in Installed tag, Reference value is MA_502, it is not SAME. MA_502 != MA_50 So, it should return two matches.

I tried your code #15, with Reference value MA_502, it returns 3 values. There should not be third line. When i tried this, " Equipment[Reference=='$ref' " , it is not possible.

In addition, i have 850 Mb File. I made 4 parts of 200 Mb. When i tried in Part1, it went into infinite loop.

It seems Catalog is not present in Part3 and Part4. How to solve this ?

us2rn4m2 · August 16, 2013

It's a little bit heavy...

$xml = <<<XML
<?xml version="1.0" encoding="utf-8"?>
<Physical>
   <Catalog>
	  <EquipmentSpec>
		 <Reference>MAT_48985</Reference>
		 <Type>Duplexeur</Type>
		 <Characteristic><CharacteristicName>Tilt</CharacteristicName>
		 <CharacteristicValue>0</CharacteristicValue></Characteristic>
	  </EquipmentSpec>
	  <EquipmentSpec>
		 <Reference>MAT_6890</Reference>
		 <Type>Node</Type>
		 <Characteristic><CharacteristicName>Tilt</CharacteristicName>
		 <CharacteristicValue>2</CharacteristicValue></Characteristic>
	  </EquipmentSpec>
	  <EquipmentSpec>
		 <Reference>MAT_50</Reference>
		 <Type>Single</Type>
		 <Characteristic><CharacteristicName>Tilt</CharacteristicName>
		 <CharacteristicValue>5</CharacteristicValue></Characteristic>
	  </EquipmentSpec>
   </Catalog>
   <Installed>
	  <Equipment>
		 <Id>26</Id><Reference>MAT_4</Reference>
		 <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>160</CharacteristicValue></Characteristic>
	  </Equipment>
	  <Equipment>
		 <Id>27</Id><Reference>MAT_6890</Reference>
		 <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>140</CharacteristicValue></Characteristic>
	  </Equipment>
	  <Equipment>
		 <Id>28</Id><Reference>MAT_50</Reference>
		 <Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
	  </Equipment>
   </Installed>
</Physical>
XML;

$e = new SimpleXMLElement($xml);
$values = array(
	'Type'   => array(), 
	'Id'     => array(), 
	'Height' => array()
);
	
foreach ($e->Catalog->EquipmentSpec as $equipmentSpec) {
   $values['Type'][] = (string) $equipmentSpec->Type; 
}

foreach ($e->Installed->Equipment as $equipment) {
   $values['Id'][] = (string) $equipment->Id;
   foreach ($equipment->Characteristic as $characteristic) {
       if ($characteristic->CharacteristicName == 'Height') {
			$values['Height'][] = (string) $characteristic->CharacteristicValue; 
       }   
   }       
}

$sql = array();
foreach($values as $array) {
	foreach($array as $k => $v) {
		if(!array_key_exists($k, $sql)) {
			$sql[$k] = "INSERT INTO machines (`Type`,`Id`,`Height`) VALUES ('" . $values['Type'][$k] . "','" . $values['Id'][$k] . "', '" . $values['Height'][$k] . "')";	
			// $req = new requete($site->db, $sql[$k]);
			echo $sql[$k] . '<br>';		
			/* 	output:
				INSERT INTO machines (`Type`,`Id`,`Height`) VALUES ('Duplexeur','26', '160')
				INSERT INTO machines (`Type`,`Id`,`Height`) VALUES ('Node','27', '140')
				INSERT INTO machines (`Type`,`Id`,`Height`) VALUES ('Single','28', '180')	
			*/				
		}
	}			
}

print_r($sql);
/*
Array
(
    [0] => INSERT INTO machines (`Type`,`Id`,`Height`) VALUES ('Duplexeur','26', '160')
    [1] => INSERT INTO machines (`Type`,`Id`,`Height`) VALUES ('Node','27', '140')
    [2] => INSERT INTO machines (`Type`,`Id`,`Height`) VALUES ('Single','28', '180')
)
*/

Edited August 16, 2013 by us2rn4m2

Barand · August 16, 2013

I used the XML you gave in reply #8 in which your expected results were given as

RefId**    Type          Id    Height
1       Duplexeur    26     160
2              Node       27     140
3         Single      28    180
// **RefId is column auto_incriment to count rows.

Mine gave

MAT_4    | Duplexeur | 26 | 160
MAT_6890 | Node      | 27 | 140
MAT_50   | Single    | 28 | 180

If my maths is right, that's THREE

Edited August 16, 2013 by Barand

Barand · August 16, 2013

Running my same code on your XML in #14 gives TWO

MAT_6890 | Node | 27 | 140
MAT_50 | Single | 28 | 180

Barand · August 16, 2013

It seems Catalog is not present in Part3 and Part4. How to solve this ?

You might want to consider loading your database as two tables, one with the data from the Catalog section and the other with the Installed equipment data.

You can then JOIN on reference to get the data you want

From                        From
<Catalog>                   <Installed>
+---------------+           +-------------+
| EquipmentSpec |           | Equipment   |
+---------------+           +-------------+
| Reference  PK |----+      | Id          |
| Type          |    +------| Reference   |
+---------------+           | Height      |
                            +-------------+

Edited August 16, 2013 by Barand

PriteshP23 · August 16, 2013

How i can retrieve Type value in "From Installed" ?? Please say more about it.

Two Problems:

1. How to match exactly Ref value

2. How to READ Part3, Part4

Thanks for input.

PriteshP23 · August 19, 2013

I created "machines_type" table to store all Type Values. The main table is "machines".

I need to solve Update Query with LEFT JOIN :confused:

UPDATE machines SET machines.Type = machines_type.Type 
WHERE Reference IN (SELECT machines.Reference, machines_type.Reference
FROM machines
LEFT JOIN machines_type 
ON machines.Reference = machines_type.Reference
WHERE machines.Id IS NOT NULL )

Error:
#1241 - Operand should contain 1 column(s)

Awaiting your response,

PriteshP23 · August 19, 2013

Finally, its SOLVED...!!

UPDATE machines LEFT JOIN machines_type ON
machines.Reference = machines_type.Reference
SET machines.Type = machines_type.Type
WHERE
machines_type.Reference IS NOT NULL

THANK YOU ALL FOR YOUR HELP. ::)

Sign In

XML Read by XPath

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information