-- phpMyAdmin SQL Dump
-- version 3.5.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 11, 2013 at 08:02 AM
-- Server version: 5.5.29
-- PHP Version: 5.4.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `country_state`
--

-- --------------------------------------------------------

--
-- Table structure for table `country`
--

CREATE TABLE `country` (
  `CNAME` varchar(100) NOT NULL,
  `CCAPITAL` varchar(100) NOT NULL,
  `CAREA` bigint(20) NOT NULL,
  `CPOPULATION` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `entity`
--

CREATE TABLE `entity` (
  `CNAME` varchar(100) NOT NULL,
  `TYPE` varchar(100) NOT NULL,
  `NAME` varchar(100) NOT NULL,
  `CAPITAL` varchar(100) NOT NULL,
  `AREA` bigint(20) NOT NULL,
  `POPULATION` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

LOAD XML LOCAL INFILE 
'/Applications/MAMP/htdocs/country_state.xml'
    INTO TABLE `country`
    ROWS IDENTIFIED BY '<COUNTRY>';


LOAD XML LOCAL INFILE 
'/Applications/MAMP/htdocs/country_state.xml'
    INTO TABLE `entity`
    ROWS IDENTIFIED BY '<STATE>';
    
UPDATE `entity` SET `type`='STATE' WHERE `type`='';

LOAD XML LOCAL INFILE 
'/Applications/MAMP/htdocs/country_state.xml'
    INTO TABLE `entity`
    ROWS IDENTIFIED BY '<TERRITORY>';
    
UPDATE `entity` SET `type`='TERRITORY' WHERE `type`='';

LOAD XML LOCAL INFILE 
'/Applications/MAMP/htdocs/country_state.xml'
    INTO TABLE `entity`
    ROWS IDENTIFIED BY '<EXTERNAL>';
    
UPDATE `entity` SET `type`='EXTERNAL' WHERE `type`='';