MS Visual Studio 2012 how to import xml data into sql database. help please

Forums › IT Pro and developers › MS Visual Studio 2012 how to import xml data into sql database. help please

GamerOC

439 posts

Ultimate Geek

#136270 21-Nov-2013 10:49

Microsoft Visual Studio 2012, hi geekzone and thanks for reading this post.
Im currently studying at Ucol database design and development but im at beginner level at the moment.
I have learnt to make database and tables plus how to enter data into database, however I would like to know if someone could please help me:
I want to import a xml format database base on Endnote program and into the Microsoft Visual Studio database but i cant figured out.

thanks again and have a nice week.

TwoSeven

1630 posts

Uber Geek

Subscriber

#938417 21-Nov-2013 11:22

Do you mean you want to write a program to do the import or do you just want to open have the database import data from the XML file.

For the second technique, this can be done through SQL Server Management console. However, using Visual Studio is very similar.

In Visual Studio (I am using VS2012 for this example), in your solution open the SQL Server Object Explorer [Ctrl \ Ctrl S] and then connect to your SQL server, select navigate to your database and select your table (you can view the data in the table here).

From the SQL menu at the top of the screen, select transact sql editor, this will open a T-SQL console (it may prompt you to login first). You can then write T-SQL script to do the import. Something like:

DECLARE @someXML XML
SELECT @someXML = .... (I believe OPENROWSET is the command).. You can google this example to find out the exact syntax.

Software Engineer
(the practice of real science, engineering and management)
A.I. (Automation rebranded)
Gender Neutral
(a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

...they/their/them...

GamerOC

439 posts

Ultimate Geek

#938436 21-Nov-2013 12:18

For now im just trying to import the data from a database based EndNote xml format to my SQL database. is all the code from the Endnote Database in one file.

Later on when more confident with writing the code i will see if i can attempt to develop a software that can do that for me.

thank you so much for your reply this may be exactly the solution to my problem,

I highly appreciated that you replied to my post,

thanks again

Andrew

Ragnor

8223 posts

Uber Geek

Trusted

#938517 21-Nov-2013 16:31

Yeah basically you read the file with OPENROWSET then either store it in table variable, temp table or table then process that table to extract the columns and rows.

http://stackoverflow.com/questions/16838627/import-xml-into-sql-server

http://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

TwoSeven

1630 posts

Uber Geek

Subscriber

#938655 21-Nov-2013 20:57

GamerOC: For now im just trying to import the data from a database based EndNote xml format to my SQL database. is all the code from the Endnote Database in one file.

Later on when more confident with writing the code i will see if i can attempt to develop a software that can do that for me.

thank you so much for your reply this may be exactly the solution to my problem,

I highly appreciated that you replied to my post,

thanks again

Andrew

No worries, Its good to see that you are taking the time to learn Visual Studio before writing the code. There is a lot of good productivity to be had by learning to use ones toolset effectively and maximise its value and I come across a lot of developers who only really know how to open and save code files.

...they/their/them...

GamerOC

439 posts

Ultimate Geek

#938714 21-Nov-2013 22:47

Ragnor, thank you so much for the very useful links. these links are like gold to me now and just what i was looking by using OPENROWSET.

TwoSeven, thank you so much for giving me the idea to use OPENROWSET and also for your feedback in regards my approach to learning Visual Studio 2012.

I hope one day to be able to make some neat software solutions.

thanks again,

Andrew