I have searched for a long time for the answer to the question and have gotten some results, but I continue to have problems. I have asked on other forums and no one knows how to resolve it, or I am not making myself clear, although the task seems simple enough, to import data into a database.
This is my problem:
I need to import the following XML Sample Data:
< xml version="1.0" encoding="UTF-8" >
<MyXMLFile>
<XMLHeader>
<HField1>String</HField1>
<HField2>String</HField2>
<Record>
<Table1>
<Table1Field1>String</Table1Field1>
<Table1Field2>String</Table1Field2>
</Table1>
<Table2>
<Table2PlaceHolder>
<Table2Field1>String</Table2Field1>
<Table2Field2>String</Table2Field2>
</Table2PlaceHolder>
<Table2Field3>String</Table2Field3>
</Table2>
<Table3>
<Table3Field1>String</Table3Field1>
<Table3Field2>String</Table3Field2>
</Table3>
</Record>
<Record>
<Table1>
<Table1Field1>String</Table1Field1>
<Table1Field2>String</Table1Field2>
</Table1>
<Table2>
<Table2PlaceHolder>
<Table2Field1>String</Table2Field1>
<Table2Field2>String</Table2Field2>
</Table2PlaceHolder>
<Table2Field3>String</Table2Field3>
</Table2>
<Table3>
<Table3Field1>String</Table3Field1>
<Table3Field2>String</Table3Field2>
</Table3>
</Record>
</XMLHeader>
</MyXMLFile>
I have no problems importing the data in <HField1> and <HField2>, they import nicely with I read the data into a DataSet using This Code:
Public Sub XMLImport()
' Open the dataset and read in the XML
Dim myDS As New DataSet
myDS.ReadXml("MyXMLFile.xml")
' Configure a data adapter
Dim myDA As New SqlDataAdapter
Dim myConnection As New SqlConnection
' Generate the 'INSERT' command
Dim strSQL As String = "INSERT INTO XMLHeader" & _
"([HField1]" & _
",[HField2])" & _
"VALUES" & _
"(@Value1," & _
"@Value2)"
Dim myInsertComm As New SqlCommand(strSQL, myConnection)
' Add the parameter
myInsertComm.Parameters.Add("@Value1", SqlDbType.VarChar, 15, "HField1")
myInsertComm.Parameters.Add("@Value2", SqlDbType.VarChar, 15, "HField2")
' Configure the OLE connection
With myConnection
.ConnectionString = My.Settings.TestConnectionString
.Open()
End With
' Set the insert command
myDA.InsertCommand = myInsertComm
' Perform the update
Try
myDA.Update(myDS.Tables(0))
Catch ex As SqlException
MessageBox.Show(ex.Message)
Debug.Print(ex.Message)
End Try
' Cleanup
myConnection.Close()
myConnection.Dispose()
myInsertComm.Dispose()
myDA.Dispose()
myDS.Dispose()
End Sub
Problem 1:
Where my problem arises, is when I try to import the data in <Table1Field1>. Although data exists in that XML field, I don’t know how to get it into a variable, then into the database.
Problem 2:
The Database contains Foreign References to all the other tables, so when a record is inserted into the table [XMLHeader], it generates a unique id that must be supplied to all the other tables when they are updated, so I also need to know if/how that can be done. (I guess I could create a new row in the DataSet and name it [ForeignKey] and populate it with the correct value, but I was hoping there was a better way).
If you have read the post this far, and you still understand what I need and know a resolution, you are a VB God in my eyes. (Please or great one, PLEASE share your knowledge).
Thanks in advance!
Importing XML Data into MS SQL Database (VB2005) (Not so easy)
David Kanz
Derek, thank you very much for giving me direction. I am new to XML and I am learning it a very slow pace.
To learn it, I must see it, so I googled and could not find a suitable example/tutorial/walkthrough of what you are explaining. The examples that I found, I started to code them and they were incomplete and left a lot of blanks.
I know that Microsoft has thought this out way before I posted the question, I am just apparently so lost, that I can not figure out how to do it and do not know the correct keywords to google so I can find the answer.
I am sure that other developers import data from an XML file to MS SQL everyday, I would like to humbly and respectfully request a simple example or point me in the right direction of a good through tutorial.
Thanks again.
Srini_Katta
I think your approaching this the wrong way. I think a stored procedure in SQL server would make things a lot easier for you. You'd simply call the stored procedure passing in the XML text. You would then map the XML element to fields you need using XPath, you'd also be able to determine the next ID fairly easy and you could update the related tables easier too.
If you really must use VB.NET then I wouldn't recommend storing the information in a dataset but instead query the XML Document for the elements you need. Store then in the parameters and execute the command. I know thats nowhere enough information on how to do it VB.NET but thats because I think the stored procedure approach is far better.
Shazen
Talister, here are some good articles on the topic.
Hope these help.
Alex.
NewSQL2005 User
Alex, thank you for taking the time to respond and giving your feedback. My problem is still the same. The database has been set and the XML file structure has been set to be compatible across several different platforms as well as the schema.
I truly appreciate the links provided although the were unable to give an example of how to import data from an XML file into SQL via VB.
I know that the answer is out there somewhere, if only I could find it.
Thanks