SimpleXML输出到MySQL

Question

I want to read the following XML file received from my e-commerce site and insert the records in MySQL. I tried it using simpleXML but it does not seem to be working.

<ACCOUNT NumCrds="1">
<ACCNO>HAA_00</ACCNO>
<CURRCODE>CAD</CURRCODE>
<ACCTYPE>00</ACCTYPE>
<FINAMT>0.00</FINAMT>
<BLKAMT>0.00</BLKAMT>
<AMTAVL>0.00</AMTAVL>
<CARD>
<PANNU>1234567890208108</PANNU>
<PRIMARY>Y</PRIMARY>
<CRDPRODUCT>PCAD</CRDPRODUCT>
<PROGRAMID>PTYCSH</PROGRAMID>
<CUSTCODE>00000406</CUSTCODE>
<STATCODE>02</STATCODE>
<EXDATE>2013-03-31</EXDATE>
<CRDACCNO>HAA_00</CRDACCNO>
<CRDCURRCODE>CAD</CRDCURRCODE>
</CARD>
</ACCOUNT>

<ACCOUNT NumCrds="1">
<ACCNO>XYZCESFULL9900</ACCNO>
<CURRCODE>CAD</CURRCODE>
<ACCTYPE>00</ACCTYPE>
<FINAMT>0.00</FINAMT>
<BLKAMT>0.00</BLKAMT>
<AMTAVL>0.00</AMTAVL>
<CARD>
<PANNU>0987654321756990</PANNU>
<PRIMARY>Y</PRIMARY>
<CRDPRODUCT>PCAD</CRDPRODUCT>
<PROGRAMID>PTYCSH</PROGRAMID>
<CUSTCODE>00000520</CUSTCODE>
<STATCODE>00</STATCODE>
<EXDATE>2013-04-30</EXDATE>
<CRDACCNO>XYZCESFULL9900</CRDACCNO>
<CRDCURRCODE>CAD</CRDCURRCODE>
</CARD>
</ACCOUNT>

I have written the following code that is working as expected.

<?php
$sxe = simplexml_load_file("balexp.xml");
foreach($sxe->ACCOUNT as $item) {
$mysql="insert into test.mytable values ('".$item->ACCNO."','".$item->CURRCODE."')";
echo $mysql;
}
?>

But I have 1 question to ask. 1) There are times when I need to process 2 cards in the same ACCOUNT clause. How do I handle such situations?

Answer 1

You could do it the hard way.

$dom = new DOMDocument();
$dom->loadXML('<root>'. $xmlstr .'</root>');
$xml = simplexml_import_dom($dom);

$sql = 'INSERT INTO accounts VALUES';
foreach ($xml as $node) {
    $data = array(
        (string) $node->attributes()->NumCrds,
        (string) $node->ACCNO,
        // ...
        (string) $node->CARD->PANNU,
        // ...
    );
    $sql .= " ('". implode("', '", $data) ."'),";
}
$sql = rtrim($sql, ',');

print $sql;

Consider changing some column types in your database to INT, DECIMAL, DATE and use vsprintf() to add $data to $sql.