Generating the file XML with PHP I get the following result:
I'd like to send this now to a MYSQL database by first inserting the columns in the Imovel table and then filling those columns with the data being pulled into each field .
Could anyone help me make a script to insert data from XML into MYSQL database?
I recommend you pack up this XML for a more practical way and avoid repeating the nodes. Note that even your CDATA contains undue spaces, to remove them use the TRIM
Perhaps the best option is for XML for your case to be:
<CODIGO>CL501</CODIGO>
<DATA>2012-03-16</DATA>
With the XML you have passed, the best you can do is as below:
XML
$string = '<Imovel>
<field name="CODIGO"><![CDATA[ CL501 ]]></field>
<field name="DATA"><![CDATA[ 2012-03-16 ]]></field>
<field name="ENDERECO"><![CDATA[ CASEMIRO DE ABREU ]]></field>
</Imovel>';
Mounting SQL
$root = simplexml_load_string( $string );
foreach( $root as $element )
{
foreach( $element-> attributes() as $field )
{
$clear = trim( $element );
$fields[] = "'$field'";
$values[] = "'$clear'";
}
}
SQL
INSERT INTO 'TABLE' (" . implode( ', ' , $fields ) . ")
VALUES (" . implode( ', ' , $values ) . ")
OUTPUT
INSERT INTO 'TABLE' ('CODIGO', 'DATA', 'ENDERECO')
VALUES ('CL501', '2012-03-16', 'CASEMIRO DE ABREU')