Solved

Posted on 2011-10-25
Medium Priority
187 Views
URGENT !!

Can someone give me the vb.net code to upload xml file to sql server. very urgent.thanks.
0
Question by:Monaglit
• 6
• 3

LVL 75

Expert Comment

ID: 37024093
One example:
Dim xmlData As XmlDocument ' Assume this is already initialized

Using con As New System.Data.SqlClient.SqlConnection(your_connection_string)
Using cmd As New System.Data.SqlClient("INSERT INTO [your_table_name] VALUES (@data)", con)

con.Open()
cmd.ExecuteNonQuery()
End Using
End Using

0

Author Comment

ID: 37024128
I am selecting the XML file from hard disk. in that case what will xmldata be?
0

Author Comment

ID: 37024175
when i run this i get column name or number of supplied values does not match
0

Author Comment

ID: 37024184
i need to put the element values as the column values in the database
0

LVL 75

Expert Comment

ID: 37024206
I am selecting the XML file from hard disk. in that case what will xmldata be?
You can use the XmlDocument.Load method to read a file from disk:

Dim xmlData As New XmlDocument()



i need to put the element values as the column values in the database

Oh. I got the impression from your question that you were inserting the actual XML into the database. If you need to insert the node values into specific columns, then we would need to see the XML structure and the database column names.
0

Author Comment

ID: 37025603
here is the sample xml and table columns

<Records filename="C:\Documents and Settings\Data.csv">
<Record RowID="1">
<EmployeeID>D091</EmployeeID>
<Name>hhh</Name>
<surname>sss</surname>
<DateofBirth>2004-4-12 00:00</DateofBirth>
</Record>
<Record RowID="2">
<EmployeeID>C002</EmployeeID>
<Name>sjjj</Name>
<surname>kkkk</surname>
<DateofBirth>
</DateofBirth>
</Record>
</Records>

TableName -  Employee
columns -
Employee_ID varchar(20)
Name varchar(20)
Surname varchar(50)
DateofBirth datetime
0

LVL 75

Accepted Solution

käµfm³d   👽 earned 2000 total points
ID: 37026084
OK, so let's rework my original example:

Dim xmlData As XmlDocument ' Assume this is already initialized

Using con As New System.Data.SqlClient.SqlConnection(your_connection_string)
Using cmd As New System.Data.SqlClient("INSERT INTO [Employee] VALUES (@ID, @Name, @Surname, @BDay)", con)

con.Open()

For Each record As XmlNode in xmlData.SelectNodes("//Record")
cmd.Parameters("@ID").Value = record.SelectSingleNode("EmployeeID").InnerText
cmd.Parameters("@Name").Value = record.SelectSingleNode("Name").InnerText
cmd.Parameters("@Surname").Value = record.SelectSingleNode("Surname").InnerText
cmd.Parameters("@BDay").Value = record.SelectSingleNode("DateofBirth").InnerText

cmd.ExecuteNonQuery()
Next
End Using
End Using


There is no error handling in the above. Make sure you add some to prevent NullReferenceExceptions and such.
0

Author Comment

ID: 37037900
Thanks. Already managed to do. But will accept this as the solution.
0

Author Closing Comment

ID: 37037904
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
###### Suggested Courses
Course of the Month17 days, 10 hours left to enroll