Using .net to read xml file into SQL 2000

[Inskyo]

Hi All,

Having problems reading an xml file into sql 2000. The file is broken down into header and Detail sections, the detail section containing a varying number of lines i.e. <line1> ... <\line1>, <line2> ... <\line2>.

Now i have the following code which interogates and reads the header information from the file into a sql table, but cannot find a way of reading the detail. Any help would be much appreciated.

Dim objSQLConn As New SqlConnection("SERVER=...;UID=...;PWD=...;DATABASE=.....;")
Dim objAdapter As SqlDataAdapter
Dim objAdapter_det As SqlDataAdapter
Dim objDataRow, objDBRow, objDetDB_Row, objDet_Row As DataRow
Dim Arr As Array
Dim objDSXML As New DataSet
Dim objDSDBTable As New DataSet("Tbl_XML_Header")
Dim objDSDBTable_Det As New DataSet("Tbl_XML_Detail")
Dim ObjCmdBuilder As SqlCommandBuilder
Dim str As String
Dim Mbox As MessageBox

str = Filename_Box.Text

If Filename_Box.Text = "" Then
Mbox.Show("You must enter a valid filename!")
Else
str = Filename_Box.Text
objDSXML.ReadXml(str)
objSQLConn.Open()
objAdapter = New SqlDataAdapter("SELECT * from tbl_XML_Header", objSQLConn)
objAdapter.Fill(objDSDBTable, "Tbl_XML_Header")
For Each objDataRow In objDSXML.Tables(0).Rows
With objDSDBTable.Tables(0)
objDBRow = .NewRow()
objDBRow(0) = objDataRow(0)
objDBRow(1) = objDataRow(1)
objDBRow(2) = objDataRow(2)
objDBRow(3) = objDataRow(3)
objDBRow(4) = objDataRow(4)
objDBRow(5) = objDataRow(5)
objDBRow(6) = objDataRow(6)
objDBRow(7) = objDataRow(7)
objDBRow(8) = objDataRow(8)
objDBRow(9) = objDataRow(9)
objDBRow(10) = objDataRow(10)
objDBRow(11) = objDataRow(11)
objDBRow(12) = objDataRow(12)
objDBRow(13) = objDataRow(13)
objDBRow(14) = str
.Rows.Add(objDBRow)
End With
ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
objAdapter.Update(objDSDBTable, "Tbl_XML_Header")
Next
objSQLConn.Close()
End If

 

[Inskyo]

Solved!!!....

Hi All,

Looks like i managed to solve this problem......FINALLY!!
As you can see though, I have had to put two counters in (i,i2).

The first one i kinda expected to have to with the varying number of lines in the detail, however for some reason, the detail lines were copying in twice, (i.e. duplicating each line). So if you have a "cleaner" way of preventing this then would be great to hear, but i have just got round it with the counter.

Thanks for taking the time to look at the post, much appreciated.

Yours Inskyo.

See solution code below:

objDSXML.ReadXml(str)
objSQLConn.Open()
objAdapter_det = New SqlDataAdapter("SELECT * from Tbl_XML_Detail", objSQLConn)
objAdapter_det.Fill(objDSDBTable_Det, "Tbl_XML_Detail")
i = 2
While i < 1000
i2 = 1
For Each objDataRow In objDSXML.Tables(i).Rows
If i2 = 1 Then
With objDSDBTable_Det.Tables(0)
objDet_Row = .NewRow()
objDet_Row(0) = objDataRow(0)
If objDet_Row(0) = "END OF REPORT" Then
End
End If
objDet_Row(1) = objDataRow(1)
objDet_Row(2) = objDataRow(2)
objDet_Row(3) = objDataRow(3)
objDet_Row(4) = objDataRow(4)
objDet_Row(5) = objDataRow(5)
objDet_Row(6) = objDataRow(6)
objDet_Row(7) = objDataRow(7)
objDet_Row(8) = objDataRow(8)
objDet_Row(9) = objDataRow(9)
objDet_Row(10) = objDataRow(10)
objDet_Row(11) = objDataRow(11)
objDet_Row(12) = objDataRow(12)
objDet_Row(13) = str
.Rows.Add(objDet_Row)
End With
ObjCmdBuilder = New SqlCommandBuilder(objAdapter_det)
objAdapter_det.Update(objDSDBTable_Det, "Tbl_XML_Detail")
i2 = i2 + 1
End If
Next
i = i + 1
End While