Insert XML values into Mysql

[Nandini]

Hi

 

I have a XML file as follows:

 

<?xml version="1.0"?>

<inspection_form>

  <inspection_type>

    <inspection_area_tlb>yard_and_lot</inspection_area_tlb>

    <inspection_area>Yard and Lot</inspection_area>

    <items>

      <item>

        <item_name>PID Signage/unauthorized sign on pole</item_name>

        <item_value>0</item_value>

      </item>

      <item>

        <item_name>Landscape well maintained</item_name>

        <item_value>0</item_value>

      </item>

</items>

  </inspection_type>

  <inspection_type>

    <inspection_area_tlb>pump_island</inspection_area_tlb>

    <inspection_area>Pump Island and Canopies</inspection_area>

    <items>

      <item>

        <item_name>pumps clean and free of dirt</item_name>

        <item_value>0</item_value>

      </item>

      <item>

        <item_name>Approved trash cans/clean</item_name>

        <item_value>0</item_value>

      </item>

</items>

  </inspection_type>

</inspection_form>

 

I want to insert into DB as follows:

 

inspection_area_tlb

inspection_area

item_name

item_value

yard_and_lot

yard and Lot

PID Signage/unauthorized sign on pole

0

yard_and_lot

yard and Lot

Landscape well maintained

0

pump_island

Pump Island and Canopies

pumps clean and free of dirt

0

pump_island

Pump Island and Canopies

Approved trash cans/clean

0

 

I have written some php code. But every item node as insert for every 'inspection_type'. This is my code

 

$filename="sample.xml";
if(filesize($filename)>0)
{
$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($filename)); 
foreach ($oDOM->getElementsByTagName('inspection_type') as $oBookNode)
{
foreach ($oDOM->getElementsByTagName('item') as $itmNode)
{
    $sSQL = sprintf(
        "INSERT INTO inspections_master_tablename_import (INSPECTION_TYPE_DB_C_NAME, INSPECTION_TYPE_C_NAME, INSPECTION_TYPE_ITEM_C_NAME,INSPECTION_TYPE_ITEM_VALUE_C_NAME) VALUES ('%s', '%s', '%s', '%s')",
        mysql_real_escape_string($oBookNode->getElementsByTagName('inspection_area_tlb')->item(0)->nodeValue),
        mysql_real_escape_string($oBookNode->getElementsByTagName('inspection_area')->item(0)->nodeValue),
        mysql_real_escape_string($itmNode->getElementsByTagName('item_name')->item(0)->nodeValue),
	mysql_real_escape_string($itmNode->getElementsByTagName('item_value')->item(0)->nodeValue)
    );
    $rResult = mysql_query($sSQL);
    
    if(mysql_errno() > 0)
    {
        printf(
            '<h4 style="color: red;">Query Error:</h4>
            <p>(%s) - %s</p>
            <p>Query: %s</p>
            <hr />',
            mysql_errno(),
            mysql_error(),
            $sSQL
        );
    }
}
}
}

 

Can anyone help me pls.

[Adam]

First, what's the problem with it?

[Nandini]

By using this code output displaying as follows

 

inspection_area_tlb

inspection_area

item_name

item_value

yard_and_lot

yard and Lot

PID Signage/unauthorized sign on pole

0

yard_and_lot

yard and Lot

Landscape well maintained

0

yard_and_lot

yard and Lot

pumps clean and free of dirt

0

yard_and_lot

yard and Lot

Approved trash cans/clean

0

pump_island

Pump Island and Canopies

PID Signage/unauthorized sign on pole

0

pump_island

Pump Island and Canopies

Landscape well maintained

0

pump_island

Pump Island and Canopies

pumps clean and free of dirt

0

pump_island

Pump Island and Canopies

Approved trash cans/clean

0

 

But this is wrong. I want out put as like in our question