Importing xml woes

  • Thread starter Thread starter mrc
  • Start date Start date
M

mrc

Hi all,

I have the following function that imports an xml file created by a
desktop app, and sticks it into a dataset on a sqlce server. Thing is,
it takes an age to load the xml file into a dataset
(dsSource.ReadXml(myXmlProximityReader)). Is there any better way of
doing this ? Can you import the xml file directly into the sql
database rather than loading it in to memory and then writing it to the
database ?

Many thanks

Séan



Public Function LoadXMLFile(ByVal strFile As String, ByVal
strTableName As String) As DataSet
' loads the xml into the database
' and returns a dataset
' calling sub must deal with error
Dim dsSource As DataSet
Dim strSQL As String
Try
' read the xml file into a dataset
Dim fsReadProximityXml As New System.IO.FileStream _
(strFile, System.IO.FileMode.Open)
Dim myXmlProximityReader As New System.Xml.XmlTextReader _
(fsReadProximityXml)
dsSource = New DataSet
dsSource.ReadXml(myXmlProximityReader)
myXmlProximityReader.Close()
Catch e As Exception
Throw New System.Exception("Loading XML File into memory
failed: " & strFile)
End Try

strSQL = "SELECT * from " & strTableName
Dim MySrcRow As System.Data.DataRow
Dim MyDestRow As System.Data.DataRow
Dim MySrcCol As System.Data.DataColumn
Dim dsDest As New DataSet
Dim sqlDataAdapter As New SqlServerCe.SqlCeDataAdapter(strSQL,
ssceconn)
Dim sqlCommandBuilder As New
SqlServerCe.SqlCeCommandBuilder(sqlDataAdapter)
sqlDataAdapter.Fill(dsDest, strTableName)
dsSource.Tables.Contains(strTableName)
If dsSource.Tables.Contains(strTableName) = False Then
' empty xml file so just return the empty dataset
Return dsDest
Exit Function
End If
Try
' loop through each row

For Each MySrcRow In dsSource.Tables(strTableName).Rows
MyDestRow = dsDest.Tables(strTableName).NewRow
' loop through each column
For Each MySrcCol In
dsSource.Tables(strTableName).Columns
' set the coresponding dest row = to the source row
MyDestRow(MySrcCol.ColumnName) =
MySrcRow.Item(MySrcCol.ColumnName)
Next
' add the completed row
dsDest.Tables(strTableName).Rows.Add(MyDestRow)
Next
sqlDataAdapter.Update(dsDest, strTableName)
Catch e As Exception
Throw New System.Exception("Loading XML File into database
failed: " & strFile)
End Try
Return (dsDest)
End Function
 
Please see this:

http://groups.google.com/group/micr...d2019be7a0d/25764f05e7a022bc#25764f05e7a022bc

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

Hi all,

I have the following function that imports an xml file created by a
desktop app, and sticks it into a dataset on a sqlce server. Thing is,
it takes an age to load the xml file into a dataset
(dsSource.ReadXml(myXmlProximityReader)). Is there any better way of
doing this ? Can you import the xml file directly into the sql
database rather than loading it in to memory and then writing it to the
database ?

Many thanks

Séan



Public Function LoadXMLFile(ByVal strFile As String, ByVal
strTableName As String) As DataSet
' loads the xml into the database
' and returns a dataset
' calling sub must deal with error
Dim dsSource As DataSet
Dim strSQL As String
Try
' read the xml file into a dataset
Dim fsReadProximityXml As New System.IO.FileStream _
(strFile, System.IO.FileMode.Open)
Dim myXmlProximityReader As New System.Xml.XmlTextReader _
(fsReadProximityXml)
dsSource = New DataSet
dsSource.ReadXml(myXmlProximityReader)
myXmlProximityReader.Close()
Catch e As Exception
Throw New System.Exception("Loading XML File into memory
failed: " & strFile)
End Try

strSQL = "SELECT * from " & strTableName
Dim MySrcRow As System.Data.DataRow
Dim MyDestRow As System.Data.DataRow
Dim MySrcCol As System.Data.DataColumn
Dim dsDest As New DataSet
Dim sqlDataAdapter As New SqlServerCe.SqlCeDataAdapter(strSQL,
ssceconn)
Dim sqlCommandBuilder As New
SqlServerCe.SqlCeCommandBuilder(sqlDataAdapter)
sqlDataAdapter.Fill(dsDest, strTableName)
dsSource.Tables.Contains(strTableName)
If dsSource.Tables.Contains(strTableName) = False Then
' empty xml file so just return the empty dataset
Return dsDest
Exit Function
End If
Try
' loop through each row

For Each MySrcRow In dsSource.Tables(strTableName).Rows
MyDestRow = dsDest.Tables(strTableName).NewRow
' loop through each column
For Each MySrcCol In
dsSource.Tables(strTableName).Columns
' set the coresponding dest row = to the source row
MyDestRow(MySrcCol.ColumnName) =
MySrcRow.Item(MySrcCol.ColumnName)
Next
' add the completed row
dsDest.Tables(strTableName).Rows.Add(MyDestRow)
Next
sqlDataAdapter.Update(dsDest, strTableName)
Catch e As Exception
Throw New System.Exception("Loading XML File into database
failed: " & strFile)
End Try
Return (dsDest)
End Function
 
Back
Top