Inserting XML data

Question

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.

can anyone help me in this please....

Kapil Singh
– Kapil Singh

2013-06-06 10:42:46 +00:00
Commented Jun 6, 2013 at 10:42 — Kapil Singh
– Kapil Singh, Commented Jun 6, 2013 at 10:42

MvG · Accepted Answer · 2013-06-06 13:20:50Z

2

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.

answered Jun 6, 2013 at 13:20

MvG

61.7k25 gold badges158 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

Just a word of caution: Using INSERT with SET like this is MySQL specific and will not work on any other DBMS. The standard syntax for would be insert into UserApplication (ApplicationName, GroupName, UserName) values (?,?,?)

MvG Over a year ago

@a_horse_with_no_name: True. It's one part of MySQL syntax I really like, particularly in cases where values are more complicated expressions and I like to keep track of which one is which. But I agree that in this case, the benefit is negligible, so probably there is no reason to choose that non-portable syntax except to stay consistent with other code fragments which are not so simple. It's a matter of priority, depending on how likely a migration to another DBMS appears.

Collectives™ on Stack Overflow

Inserting XML data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related