CODEWITHSUNDEEP
mysqlxml
Abdul Muheet
Abdul Muheet

Reputation: 315

How to make a table in mysql for this xml and after table creation how to import?

This is my XML code I want to import it to MySql database but I don't know how to create a table for this XML so please guide me that i can import the XML code to the database. The code is given below.

 <states>
     <state name ="city1" colour="#009900">
      <point lat="475331583" lng="67.08251953125"/>
      <point lat="137335685307" lng="36328125"/>
      <point lat="044673317843" lng="67.1484375"/>
      <point lat="210237943" lng="6625"/>
    </state>

    <state name ="city2" color="#009900">
     <point lat="344.44" lng="64444"/>
     <point lat="256557" lng="4444"/>
     <point lat="25.955544" lng="67.1484375"/>
     <point lat="243" lng="665625"/>
   </state>

   <state name ="city3" color="#009900">
    <point lat="344.44" lng="64444"/>
    <point lat="256557" lng="4444"/>
    <point lat="25.955544" lng="65"/>
    <point lat="243" lng="665625"/>
  </state>
 </states>

Upvotes: 0

Views: 1762

Answers (1)

eggyal
eggyal

Reputation: 125835

To create the table, see CREATE TABLE:

CREATE TABLE states (
    name  VARCHAR(255),
    color CHAR(7),
    PRIMARY KEY (name)
);

CREATE TABLE statePoints (
    name VARCHAR(255),
    lat  DECIMAL(50,25),
    lng  DECIMAL(50,25),
    FOREIGN KEY (name) REFERENCES states (name)
);

To import the data, see LOAD XML:

LOAD XML INFILE '/path/to/file.xml'
    INTO TABLE states
    ROWS IDENTIFIED BY '<state>';

LOAD XML INFILE '/path/to/file.xml'
    INTO TABLE statePoints
    ROWS IDENTIFIED BY '<point>';

Upvotes: 3

Related Questions