Reputation: 591

Create an SQL table from XML data

I have an XML file containing some data as follow:

<?xml version="1.0" encoding="utf-8"?>
<countries>
    <country id="Canada">
        <location>
            <code>CAXX0001</code>
            <name>Abbotsford</name>
        </location>
        <location>
            <code>CAXX0002</code>
            <name>Agassiz</name>
        </location>
    </country>
    <country id="Belgium">
        <location>
            <code>BEXX0001</code>
            <name>Anderlecht</name>
        </location>
    </country>
</countries>

I need to use the data in it to create a database table (MYSQL 5.1) with the following fields

countryName: (read it from the id attribute of the country tag),
locationCode: (read it from the value of code sub-tag),
locationName: (read it from the value of name sub-tag)

Any help about the SQL syntax will be appreciated.
Thanks!

Upvotes: 0

Answers (3)

Mark

Reputation: 55

Use PHP to do this. It is really simple. :-)

$info = file_get_contents( "./MyFile.xml" );
$info = htmlspecialchars_decode( $info );
$xml = simplexml_load_string( $info );
$json = json_encode( $xml );
$array = json_decode( $json, true );

So basically, you first GET the XML, then you remove any special characters (sometimes the XML will come in as &-lt-;column&-gt-;. (Minus the dashes of course!) So you use the special characters decoding to get rid of that leaving you things like "<column>". Then you use simple XML to load the string in to an XML array. Then you use the json_encode to translate the XML into JSON. Then you use the json_decode to convert the JSON array back into an associative array. Once you have it in that form you can just use a FOREACH command to iterate through the array. I've noticed that this seems to always make an array that you iterate through that has two things in the array: NewDataSet and Table. So I always do:

 foreach( $array['NewDataSet']['Table'] as $k=>$v ){
      .   .   .
 }

It can also have blank arrays stuck in there. These are when there isn't a value for something. SO an INSERT command looks like this:

foreach( $array["NewDataSet"]["Table"] as $k=>$v ){
    $sql = "insert into categories (";
    $val = "values (";
    foreach( $v as $k1=>$v1 ){
        $sql .= "$k1,";

        if( is_numeric($v1) ){ $val .= "$v1,"; }
            else if( is_array($v1) ){ $val .= "'',"; }
            else { $val .= "'$v1',"; }
        }

    $sql = substr( $sql, 0, -1 ) . ") " . substr( $val, 0, -1 ) . ")";
    $s = dosql( $sql );
    }

But you can just change things out to do a CREATE TABLE command. For instance, the following is from a website where everything is done via XML

<BrandUpdate>
<Input>
<param name="CustomerNumber" maxlength="5" type="xs:numeric-string">Customer Number</param>
<param name="UserName" maxlength="50" type="xs:string">User Name</param>
<param name="Password" maxlength="15" type="xs:string">Password</param>
<param name="Source" maxlength="8" type="xs:string">Description of source using service</param>
</Input>
<Output>
<DataSet>
<xs:schema id="NewDataSet">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded"><xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="BRDNO" type="xs:decimal" minOccurs="0" MaxLength="4,0" Description="Brand Id"/>
<xs:element name="BRDNM" type="xs:string" minOccurs="0" MaxLength="50" Description="Brand Name"/>
<xs:element name="BRDURL" type="xs:string" minOccurs="0" MaxLength="50" Description="Brand URL"/>
<xs:element name="ITCOUNT" type="xs:int" minOccurs="0" Description="Count of Brand Items"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element></xs:schema>
</DataSet>
</Output>
</BrandUpdate>

As you can see - all of the information to set up a CREATE TABLE command is there already. All you have to do is to go to the MySQL website, get how to layout a CREATE TABLE command, and then cycle through the array with the FOREACH command and set up the command to create the table entry. The last thing you do is to execute the MySQL command.

I use the following when working with MySQLi. It is pretty simple but works

################################################################################
#   dosql(). Do the SQL command.
################################################################################
function dosql( $sql )
{
    global $mysqli;

    echo "SQL = $sql\n";
    $res = $mysqli->query( $sql );
    if( !$res ){
        $ary = debug_backtrace();
        if( isset($ary[1]) ){ $a = $ary[1]['line']; }
            else if( isset( $ary[0]) ){ $a = $ary[0]['line']; }
            else { $a = "???"; }

        echo "ERROR @ " . $a . " : (" .  $mysqli->errno . ")\n" . $mysqli->error . "\n\n";
        echo "SQL = $sql\n";
        exit;
        }

    if( preg_match("/insert/i", $sql) ){ return $mysqli->insert_id; }
    if( preg_match("/delete/i", $sql) ){ return true; }
    if( !is_object($res) ){ return null; }

    $cnt = -1;
    $ary = array();
    $res->data_seek(0);
    while( $row = $res->fetch_assoc() ){
        $cnt++;
        foreach( $row as $k=>$v ){ $ary[$cnt][$k] = $v; }
        }

    return $ary;
}

And I open a connection via:

echo "Establishing a connection to the database...please wait.\n";
$mysqli = new mysqli( "<HOST>", "<USR>", "<PWD>", "<TABLE>" );
if( $mysqli->connect_errno ){
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    exit;
    }

I hope this helps you out a bit. :-)

Upvotes: 0