CODEWITHSUNDEEP
mysqlsql
Kapil Singh
Kapil Singh

Reputation: 107

Inserting XML data

I have a xml file:

<root>
<application>
    <app-name>Google App</app-name>
    <group>
        <group-name>MidasGoogleAppsUsers</group-name>
        <user>
            <username>knikkhoo</username>
            <username>devendrat</username>
        </user>
    </group>
</application>
<application>
    <app-name>MySql App</app-name>
    <group>
        <group-name>MidasSQLUsers</group-name>
        <user>
            <username>knikkhoo</username>
            <username>devendrat</username>
        </user>
    </group>
</application>
<application>
    <app-name>ESNutritio</app-name>
    <group>
        <group-name>MidasNutrition</group-name>
        <user>
            <username>knikkhoo</username>
            <username>devendrat</username>
        </user>
    </group>
</application>
 </root>

I have a table named test_xml with strucutre in which I have stored this XML:

Create table test_xml
 (
  ID int,
  XMLData text
 )

There is another table UserAppliaction

 create table `test`.`UserApplication`( 
  `ID` int NOT NULL AUTO_INCREMENT , 
  `ApplicationName ` varchar(100) , 
  `GroupName` varchar(100) , 
  `UserName` varchar(100) , 
  PRIMARY KEY (`ID`)
  )

How can I stored this data into UserApplication table. I am new to Mysql.

Upvotes: 1

Views: 61

Answers (1)

MvG
MvG

Reputation: 60858

Don't try to parse the XML in SQL. Use your favorite application programming language to parse that thing, then insert the extracted data using SQL queries like this one:

INSERT INTO UserApplication
SET ApplicationName = ?,
    GroupName = ?,
    UserName = ?

In most language bindings, you can actually enter the statement like this, including the quotation marks, and then execute the statement repeatedly passing different values for these placeholders. The library will take care of properly escaping all your data. Often this approach is called “prepared statements”, since preparation and execution of the statement are to distinct steps.

Upvotes: 2

Related Questions