-1

my XML is as below

<employees>
    <employee email="email1" dateofbirth="1900-01-01" lastname="lastname1" firstname="firstname1" />
    <employee email="email2" dateofbirth="0001-01-01" lastname="lastname2" middleinitial="2" firstname="firstname2" />
</employees>

And my table [dbo.Employee] in the DB has the columns below 

FirstName 
LastName
DOB

The above XML data is available to me in an XmlDocument object. I would like to save the above data in the respective columns in the SQL table. Please help..

Chief Wiggum
  • 2,784
  • 2
  • 31
  • 44
Akshatha
  • 592
  • 1
  • 11
  • 28
  • 3
    loop through the nodes, save the node value to a variable, and pass those into the query – Harry Nov 12 '14 at 10:33

2 Answers2

0

There are multiple options to upload XML file to SQL SERVER table.

1) You can use C# and SQL SERVER to achieve the same. Check this URL: http://www.aspdotnet-suresh.com/2012/12/aspnet-send-xml-file-as-parameter-to.html

2) Bulk insert functionality of SQL SERVER. Check this URL: http://msdn.microsoft.com/en-IN/library/ms191184.aspx

3) Using Data flow task or SSIS. Check this URL: How to load an XML file into a database using an SSIS package?

Community
  • 1
  • 1
Paresh J
  • 2,401
  • 3
  • 24
  • 31
  • Thanks for the help.. The first approach seems apt but the issue here is that XmlReader uses a file to read the xml from where as my xml data is present inside the XmlDocument object. I could have written the data onto another file and then passed the file to XmlReader but that is not the right solution to my project. Is there anyway where i can use the data in XmlDocument object without creating any intermediate files? – Akshatha Nov 12 '14 at 12:16
0

I had the same problem with you over the past week. What I ended up doing is using Python's pyodbc SQL connector to connect to the database and xml parser to parse the text from an xml file.

I believe you can do exactly the same using C#.

Here you can find out about the XML parser in C#.

Then you can connect to the database the way it is described in this link.

So, what you will end up doing, is iterating through the xml, using xml parser and then constructing the correct sql statement for inserting into the database.

In your example, the desired SQL code should look something like this:

INSERT INTO Employee (FirstName,LastName,DOB)
VALUES ('firstname1','firstname2','1900-01-01')

where the values inside quotes will be produced dynamically through the XML parser.

fcdimitr
  • 528
  • 3
  • 16
  • Thanks for the help.. The solution you have suggested works if I have a definite file from where the data is being read. Apparently that is not the case in my issue. Different kinds of data will be in different files and the names may vary every time the file is uploaded. So I cannot rely on the file name. This limits the usage of XmlReader. – Akshatha Nov 12 '14 at 12:18