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

uploading xml to database vb.net

URGENT !!

Can someone give me the vb.net code to upload xml file to sql server. very urgent.thanks.
0
Monaglit
Asked:
Monaglit
  • 6
  • 3
1 Solution
 
käµfm³d 👽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)
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@data", xmlData.OuterXml))

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

Open in new window

0
 
MonaglitAuthor Commented:
I am selecting the XML file from hard disk. in that case what will xmldata be?
0
 
MonaglitAuthor Commented:
when i run this i get column name or number of supplied values does not match
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
MonaglitAuthor Commented:
i need to put the element values as the column values in the database
0
 
käµfm³d 👽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()

xmlData.Load("C:\path\to\file.xml")

Open in new window


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

Open in new window

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
 
MonaglitAuthor 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
 
käµfm³d 👽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)
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ID", Nothing))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", Nothing))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Surname", Nothing))
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BDay", Nothing))

        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

Open in new window


There is no error handling in the above. Make sure you add some to prevent NullReferenceExceptions and such.
0
 
MonaglitAuthor Commented:
Thanks. Already managed to do. But will accept this as the solution.
0
 
MonaglitAuthor Commented:
good upload script.
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now