Jump to content

XML Read by XPath


PriteshP23
Go to solution Solved by PriteshP23,

Recommended Posts

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;

}
Link to comment
Share on other sites

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 by sKunKbad
Link to comment
Share on other sites

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...
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. :-\

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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,

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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 by us2rn4m2
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

 

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 by Barand
Link to comment
Share on other sites

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,

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.