XML Read by XPath DOM

[Pritesh23]

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

XML File:

Code: Select all

<?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:

Code: Select all

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);

THANKS IN ADVANCED FOR YOUR INPUT.

[johnj]

This article should help you:
http://phillipnb.wordpress.com/2011/05/ ... ml-part-3/

[Pritesh23]

Well, i have already seen this article. Thanks.

[johnj]

that means you did not see this article properly.

[Pritesh23]

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

Code: Select all

<?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>

EXPECTED output:

Code: Select all

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.

Thanks in advanced.

[johnj]

Code: Select all

$dom = new DomDocument();
$dom->load("test_xml.xml"); // your xml file

$xpath  = new DOMXPath($dom);
$result   = $xpath->query("/Physical/Catalog/EquipmentSpec/Type");

echo '<br/><br/>';
foreach ($result as $element) 
{
    echo "<br/> Type = ".$element->nodeValue."\n";
}

$result   = $xpath->query("/Physical/Installed/Equipment/Id");

echo '<br/><br/>';
foreach ($result as $element) 
{
    echo "<br/>Id = ".$element->nodeValue."\n";
}

[Pritesh23]

Thanks. But It is giving result:

Code: Select all

<br/><br/><br/><br/>

I think We need to correct this:

Code: Select all

echo "<br/> Type = ".$element->nodeValue."\n";

[johnj]

It is already there as echo "<br/> Type = ".$element->nodeValue."\n";

[johnj]

I am getting the right Id and Type as output. If you are not getting it then you are doing something wrong somewhere.

[Pritesh23]

Can you tell me OUTPUT for this xml file ??

Code: Select all

<?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>

Awaiting your response,

[johnj]

Why don't you try?. I showed you an example and also gave a link to the article that teaches you how to do it.

[Pritesh23]

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

XML

Code: Select all

<?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:

Code: Select all

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

Code: Select all

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. Thanks for input.

[johnj]

if it is not inserting write error trap routine/statements and trap the error.

[Pritesh23]

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

I need to solve Update Query with LEFT JOIN

Code: Select all

 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)

[Pritesh23]

Finally, its SOLVED.....!!!!

Code: Select all

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