How can i import xml data to sql server 2008 table ?

Question

5.00/5 (1 vote)

See more:

i have a xml file
named

data.xml
data in this file like...

XML

<countryData>
   <Country>
      <CName>AMERICAN SAMOA</CName>
      <CCode>AS</CCode>
   </Country>
   <Country>
      <CName>ANDORRA</CName>
      <CCode>AD</CCode>
   </Country>
   <Country>
      <CName>ANGOLA</CName>
      <CCode>AO</CCode>
   </Country>
   <Country>
</countryData>

now i want to insert that data in table

Table Defination like..

Id int
CName varchar(20)
CCode nchar(2)

then how can i import xml data to Table.....

plese help...

thnx

Posted 28-Jan-12 2:29am

PKriyshnA

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wonde Tadesse · Answer 1 · 2012-01-28T04:56:00

Solution 3

You can use ADO.NET DataSet ReadXml method. Here is MSDN example. DataSet.ReadXml[^]

Posted 28-Jan-12 4:56am

Wonde Tadesse

manu g m · Answer 2 · 2012-01-28T19:38:00

Solution 5

This might help you
http://csharp.net-informations.com/xml/xml-to-database.htm[^]

Posted 28-Jan-12 19:38pm

manu g m

Not Active · Answer 3 · 2012-01-28T02:39:00

Solution 1

That depends on how you want to import it; via code or SSIS.

For SSIS you may want to look at this http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx[^]

For code there is nothing special. Parse the xml and insert.

If you give a little more detail maybe we can give you a more specific answer

Posted 28-Jan-12 2:39am

Not Active

Updated 28-Jan-12 3:08am

v2

Comments

PKriyshnA 28-Jan-12 9:21am

simply i have a data in xml file but i want to use that data in project as a sql server database

so i want to import xml data in sql server 2008 database table

DATA IN XML IS:
<countrydata>
<country>
<cname>AMERICAN SAMOA
<ccode>AS

<country>
<cname>ANDORRA
<ccode>AD

<country>
<cname>ANGOLA
<ccode>AO

<country>

and DATA IN TABLE SHOULD BE:

ID CName CCode
1 AMERICAN SAMOA AS
2 ANDORRA AD
3 ANGOLA AO

[no name] 28-Jan-12 10:03am

Restating your question doesn't help to clarify anything.