XML import to SQL Server 2005

I am tyring to create either a stored procedure in SQL Server 2005 or a VB application that I can call through command line that will import an xml file into SQL Server 2005.  I have hundreds of xml files to import and I was also provided with the Schema in an xsd file.  I have been able to generate the database tables based on the schema, but nothing I have tried works for importing the data.  Does anyone have working sample code for the Import?  Thank You.
epitchesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sander StadSysteemontwikkelaar, Database AdministratorCommented:
Here is an article that can help you. It is a tutorial how to bulk load xml documents into a database.
http://technet.microsoft.com/en-us/library/ms171806.aspx 
0
Muhammad KashifDevelopment ManagerCommented:
Following code will read From XML file and write to sql server.
    Public Sub Read_XML_File()
 
        Dim ds As New DataSet
 
        'Read File to Data Set
        ds.ReadXml("FileName.xml")
 
        Dim TableCounter As Integer = 0
        Dim dr As DataRow
        Dim dataTableXml As Data.DataTable
        Dim sqlCmd As String = ""
        Dim ColCounter As Integer = 0
 
        For TableCounter = 0 To ds.Tables.Count - 1
            dataTableXml = ds.Tables(TableCounter)
            For Each dr In dataTableXml.Rows
                sqlCmd = ""
                sqlCmd = "insert into [" & dataTableXml.TableName & "] ("
 
                For i As Integer = 0 To dataTableXml.Columns.Count - 1
                    sqlCmd = sqlCmd + dataTableXml.Columns(i).ColumnName.ToString() + ","
                Next
                sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ") values ("
 
                For x As Integer = 0 To dataTableXml.Columns.Count - 1
 
 
                    Dim d As System.DateTime
                    Dim isDate As Boolean
                    If (DateTime.TryParse(dr(x).ToString(), d)) Then    'Checks If the Column is of Type Date
                        isDate = True
                    Else
                        isDate = False
                    End If
                    If (isDate) Then
                        sqlCmd = sqlCmd + "'" + Format(CDate(d.ToString), "dd/MMM/yyyy") + "',"
                    ElseIf InStr(dr(x).ToString, "'", CompareMethod.Text) <> 0 Then
                        sqlCmd = sqlCmd + "'" + dr(x).ToString().Replace("'", "''") + "',"
                    Else
                        sqlCmd = sqlCmd + "'" + dr(x) + "',"
                    End If
 
                Next
                sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ");"
                'Now Insert Query is prepared
                'Run This query; Record will be inserted
            Next
        Next
 
 
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
epitchesAuthor Commented:
Thank you!! This worked great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.