[AccessD] Importing XML into a database

Holy smoke batman!  Will this do the same to SQL Server if I change the
connection? 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Friday, October 05, 2007 3:01 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Importing XML into a database

Hello John,

Here is the code to store your XML file (let's call it clsLogData.xml) into
MS Access database "testXML.mdb" in table named [clsLogData] with field
names equal to XML file elements' names:

Imports System.Data
Imports System.Data.OleDb

Module TestImportXmlFile
    Sub Main()
        Dim rootDir As String = "E:\Temp\XML\"
        Dim accDbFileName As String = "testXML.mdb"
        Try
            Dim accConnection As String = _
                 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _
                 rootDir + accDbFileName
            Dim ds As New DataSet()
            Dim xmlFileFullPath As String = rootDir + "clsLogData.xml"
            ds.ReadXml(xmlFileFullPath)
            Using cnn As New OleDbConnection(accConnection)
                Dim cmd As New OleDbCommand("select * from [clsLogData]")
                Dim adapter As New OleDbDataAdapter(cmd)
                Dim builder As New OleDbCommandBuilder(adapter)
                cnn.Open()
                cmd.CommandType = CommandType.Text
                cmd.Connection = cnn
                adapter.Update(ds, "clsLogData")
                cnn.Close()
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

    End Sub

End Module


Of course XML file has to have a root element if you wanted to import
several records....

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, October 05, 2007 8:53 PM
To: 'Access Developers discussion and problem solving';
dba-sqlserver at databaseadvisors.com
Subject: [AccessD] Importing XML into a database

I have found / written code in VB.Net to allow a class to write (and read)
it's data out to a text file on disk.  I use this ATM to log my import
process.  When I am done I have a log file per file imported.  I will
eventually be switching to a direct write to a data table but for now I need
to know if I can import these log files into a table.  The XML produced by
the code looks like the following (actual file written):

<?xml version="1.0"?>
<clsLogData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <pTblName>AK</pTblName>
  <pStatus>Complete</pStatus>
  <pCommittedID>205453</pCommittedID>
  <pFileSpec>\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.txt</pFileSpec>
  <pStartTime>2007-10-03T12:55:49.890375-04:00</pStartTime>
  <pStopTime>2007-10-03T12:57:15.49325-04:00</pStopTime>
  <pStartID>0</pStartID>
  <pStopID>205453</pStopID>
  <pMemo>90071218 : 10/3/2007 12:55:49 PM : Read Directory: TaxRolls
90071234 : 10/3/2007 12:55:49 PM : Loading CSVReader10/3/2007 12:55:49 PM
90071234 : 10/3/2007 12:55:49 PM : Read File:
\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.txt
90071250 : 10/3/2007 12:55:49 PM : File Loaded:
\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.txt
90071296 : 10/3/2007 12:55:49 PM : Bulk Copy: tblTaxRolls 90116640 :
10/3/2007 12:56:35 PM : Bulk Copy Finished: tblTaxRolls
90116656 : 10/3/2007 12:56:35 PM : RECORDS READ: 205453
90116656 : 10/3/2007 12:56:35 PM : RECORDS BULK COPIED: 205453
10/3/2007 12:56:35 PM : Copy Temp table AK_X to the permanent table
tblTaxRolls
10/3/2007 12:57:15 PM : Temp table copied AK_X to the permanent table
tblTaxRolls
10/3/2007 12:57:15 PM : DROPPED Temp table AK_X
90156765 : 10/3/2007 12:57:15 PM : Move to Archive:
\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\Archive\AK.txt</pMemo>
</clsLogData>

Can SQL Server just read these files?  Does anyone have any VB.Net code to
read a file such as this and append the data to a table?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com