XML Read by XPath DOM

Question

PriteshP23 0 Posting Whiz in Training

11 Years Ago

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

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

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

XML:

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

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;

    }

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.

THANKS A LOT IN ADVANCED FOR YOUR INPUT.

html-css php xml

Edited 11 Years Ago by pritaeas because: Moved to PHP.

3 Contributors
26 Replies
270 Views
5 Days Discussion Span
Latest Post 11 Years Ago Latest Post by PriteshP23

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

On line 20 you have:

$req = new requete($site->db, $sql)

What is this requete class?

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

So what's the problem, the query fails? Or the variables aren't filled correctly?

Szabi Zsoldos 26 Learner and helper guy

11 Years Ago

print out the $spec first

print_r($spec) and see your variables

Szabi Zsoldos 26 Learner and helper guy

11 Years Ago

Did you try using SimpleXML ?

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Pseudo code for what you want is like this:

foreach ($spec->xpath('//Catalog/EquipmentSpec') as $sub)
{
    $Type = $sub->Type;
    $Reference -> $sub->Reference;

    // use $Reference in an XPath to find the matching Installed node
    // //Installed/Equipment/Reference[text() = '$Reference']
    // get the node's parent
    // get height and value

    // insert your data row here
}

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

PriteshP23 0 Posting Whiz in Training · Answer 1 · 2013-08-14T12:41:14+00:00

Well, it is just to run query. It is/will not affect the code for sure.

Thanks.

PriteshP23 0 Posting Whiz in Training · Answer 2 · 2013-08-14T12:55:22+00:00

The variables aren't filled correctly.

Expected output is to get RESPECTIVE "Type" from Catalaog which has same Reference from Installed. It is not working.

It is not inserting any rows.

Can you correct the code, please?

PriteshP23 0 Posting Whiz in Training · Answer 3 · 2013-08-14T13:31:07+00:00

print_r($spec) and see your variables

@szabizs:

It is not giving any Values. How to solve this problem?

Thanks in adv.

PriteshP23 0 Posting Whiz in Training · Answer 4 · 2013-08-14T14:49:12+00:00

I must use Xpath to get height value. I have used DOM. I think for complex XML file, DOM is the best option.

Szabi Zsoldos 26 Learner and helper guy · Answer 5 · 2013-08-14T20:30:02+00:00

Here you go, a working example :)

Your test.xml is below

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <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>

Then your simple xml function continues

<?php

$xml = simplexml_load_file("test.xml"); 

/**
* You can use an array and iterate over it for your database if you want to.
* $array = array();
*/
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;
            }
        }
    }
}                            

?>

PriteshP23 0 Posting Whiz in Training · Answer 6 · 2013-08-15T13:49:28+00:00

It sounds good.

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

Try this XML:

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

Can you tell me OUTPUT for this xml file ??

Awaiting your response,

Szabi Zsoldos 26 Learner and helper guy · Answer 7 · 2013-08-15T20:56:29+00:00

Use my script from above and it will give you these details :)

PriteshP23 0 Posting Whiz in Training · Answer 8 · 2013-08-16T10:01:11+00:00

Well, it is better than before. But 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. Thanks for input.

PriteshP23 0 Posting Whiz in Training · Answer 9 · 2013-08-16T16:09:46+00:00

Finally,

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;
            $AzimutObj = $inst->xpath("Characteristic[CharacteristicName='Azimut']");
            $Azimut = (string)$AzimutObj[0]->CharacteristicValue;
            echo "$ref | $type | $id | $Azimut<br>";
            // insertion to db here
        }
    }
}

OUTPUT:

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

IT must be two:

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

Because MAT_4!=MAT_48985

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 ?

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 10 · 2013-08-16T16:19:44+00:00

Hard to say without knowing how the XML looks for those parts. You cannot just split an XML file, it will lose it's structure.

Depending on whether you are running this on a dedicated host, have you considered the fact that PHP might not be the logical choice to parse an 850mb file?

PriteshP23 0 Posting Whiz in Training · Answer 11 · 2013-08-16T22:36:27+00:00

I had taken care of structure for XML file. There is no problem.

Two Problems:
1. How to match exactly Ref value
2.How to READ Part3, Part4

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 12 · 2013-08-16T23:59:36+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

I'm not psychic, I don't know what parts 3 and 4 look like.

PriteshP23 0 Posting Whiz in Training · Answer 13 · 2013-08-19T08:19:57+00:00

I'm not psychic, I don't know what parts 3 and 4 look like.

Part3, Part4 contains :

<Physical>
<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>
<Equipment>
<Id>28</Id><Reference>MAT_670</Reference>
<Characteristic><CharacteristicName>Height</CharacteristicName><CharacteristicValue>180</CharacteristicValue></Characteristic>
</Equipment>
/* ...... */
/* ...... There are only Equipments.. */
</Installed>
</Physical>

There is no Catalog tag present in the file.

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 14 · 2013-08-19T08:42:35+00:00

The above code doesn't work on this, because the Catalog part is missing.

PriteshP23 0 Posting Whiz in Training · Answer 15 · 2013-08-19T10:02:43+00:00

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

I need to solve Update Query with LEFT JOIN

SQL:

     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)

:( :P

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 16 · 2013-08-19T11:13:03+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Remove one reference in the sub-select.

PriteshP23 0 Posting Whiz in Training · Answer 17 · 2013-08-19T11:29:27+00:00

#1093 - You can't specify target table 'machines' for update in FROM clause

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 18 · 2013-08-19T11:56:22+00:00

This is completely unrelated to the rest of the thread. Explain clearly what you have and what you are trying to achieve.

PriteshP23 0 Posting Whiz in Training · Answer 19 · 2013-08-19T12:37:04+00:00

This is completely unrelated to the rest of the thread. Explain clearly what you have and what you are trying to achieve.

I clearly said that i have error in SQL query and you also know well that thing. As you told:

Remove one reference in the sub-select.

AFTER removing reference in the sub-select, i got another error:

#1093 - You can't specify target table 'machines' for update in FROM clause

SO, it is very clear that i need to solve this QUERY.

Thank you.

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 20 · 2013-08-19T12:39:19+00:00

SO, it is very clear that i need to solve this QUERY.

Yes, to you. Without more information on what you want to update, what data you have and how the tables look, I can only guess at what's happening.

The error message means that you cannot use the same table in the sub-select and the update at once.

PriteshP23 0 Posting Whiz in Training · Answer 21 · 2013-08-19T14:25:38+00:00

Finally, it's 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.