?
Solved

uploading xml to database vb.net

Posted on 2011-10-25
9
Medium Priority
?
187 Views
Last Modified: 2012-07-02
URGENT !!

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

Expert Comment

by:käµfm³d 👽
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)
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@data", xmlData.OuterXml))

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

Open in new window

0
 

Author Comment

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

Author Comment

by:Monaglit
ID: 37024175
when i run this i get column name or number of supplied values does not match
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Monaglit
ID: 37024184
i need to put the element values as the column values in the database
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
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()

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
 

Author Comment

by:Monaglit
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

by:
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)
        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
 

Author Comment

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

Author Closing Comment

by:Monaglit
ID: 37037904
good upload script.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: Kraeven
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

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question