• Status: Solved
• Priority: Medium
• Security: Public
• Views: 190

URGENT !!

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

Commented:
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 Commented:
I am selecting the XML file from hard disk. in that case what will xmldata be?
0

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

Author Commented:
i need to put the element values as the column values in the database
0

Commented:
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 Commented:
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

Commented:
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 Commented:
Thanks. Already managed to do. But will accept this as the solution.
0

Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.