用php将XML数据插入mysql

Question

Portion of xml file that represents the problem (the xml file has hundreds of customers record)

    <?xml version="1.0" encoding="utf-8"?>
    <test>
       <customer>   
          <name>customer 1</name>
          <address>address 1</address>
          <city>city 1</city>
          <state>state 1</state>
          <zip>zip 1</zip>
          <phone>phone 1</phone>
          <buyerinfo>
             <shippingaddress>
               <name>ship to</name>
               <address>Ship address1</address>
             </shippingaddress>
           </buyerinfo>
           <shippingDetail>
             <saletax>
               <saletaxamount>2</saletaxamount>
             </saletax>
           </shippingDetail>
       </customer>...

Below is my code

 //Xml string is parsed and creates a DOM Document object
    $responseDoc = new DomDocument();        
    $responseDoc->load('test.xml');     
    foreach ($responseDoc->getElementsByTagName('customer') as $customer){

     $sSQL = sprintf(
    "INSERT INTO customer (name, address, city, state, zip, phone, shipto, shipadderss, tax) 
    VALUES ('%s','%s', '%s', '%s','%s','%s', '%s','%s','%s')",
    mysql_real_escape_string($customer->getElementsByTagName('name')->item(0)->nodeValue),
    mysql_real_escape_string($customer->getElementsByTagName('address')->item(0)->nodeValue),
    mysql_real_escape_string($customer->getElementsByTagName('city')->item(0)->nodeValue),
    mysql_real_escape_string($customer->getElementsByTagName('state')->item(0)->nodeValue),
    mysql_real_escape_string($customer->getElementsByTagName('zip')->item(0)->nodeValue),
    mysql_real_escape_string($customer->getElementsByTagName('phone')->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
    );
}

    }

Questions:

1. How do I get access to get customer.buyerinfo.shippingaddress.name node value using mysql_real_escape_string in my insert statement? indicated with "???"

   The fact that I have two nodes with the same node name "name", one is customer.name and another is customer.buyerinfo.shippingaddress.name to name make it problematic to use getElementsByTagName "name" tag to get the value.

2. the same as the first one but how do I get saletaxamount node data value?

   Please kindly help. Thank you!

Answer 1

you can get child node value with a loop inside shippingaddress node.

Something like, after mysql_real_escape_string($customer->getElementsByTagName('phone')->item(0)->nodeValue)

add:

foreach ($customer->getElementsByTagName('buyerinfo') as $buyerinfo)
{
    foreach ($buyerinfo->getElementsByTagName('shippingaddress') as $shippingaddress)
    {
        mysql_real_escape_string($shippingaddress->getElementsByTagName('name')->item(0)->nodeValue),
        mysql_real_escape_string($shippingaddress->getElementsByTagName('address')->item(0)->nodeValue)
    }
}

of course you will have to fix query with new values.

this should your job, sorry i haven't a chance to test it