Copying XML File Into SQL

Hi, I have been having fun and games for well over a week now trying to get an xml file copied into an sql file, but still have no joy. I and using the bulk copy to do this and think I am close to solving it but just need a final push in the write direction.

In green below is the full source code, I think that the trouble is with the try statement part here:





 


           
Try


                bulk.WriteToServer(xd)

 


This comes up with the error :


Unable to cast object of type 'System.Xml.XmlDocument' to type 'System.Data.IDataReader'


 


Basically how do I get the XML data into a reader (or IDataReader) format so that the writetoserver command can interpret it


 


I would be so greatful if someone could help resolve this it is becoming increasingly more frustrating


  Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim objXML
Dim objXSL
' destination table

Dim connectionString As String = ConfigurationManager.ConnectionStrings("Database1ConnectionString").ConnectionString
Dim myNewConnection As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connectionString)
myNewConnection.Open()

Dim productreader As XmlReader
Dim bulk As Data.SqlClient.SqlBulkCopy = New Data.SqlClient.SqlBulkCopy(myNewConnection)
bulk.DestinationTableName = "Product"
Dim productcount As Integer = 0

Dim settings As New System.Xml.XmlReaderSettings()
settings.IgnoreWhitespace = True
settings.IgnoreComments = True

Dim xs As String = (Server.MapPath("~/App_Data/XMLfile.xml"))
Using reader As XmlReader = XmlReader.Create(xs, settings)
While (reader.Read())
If (reader.NodeType = XmlNodeType.Element And "product" = reader.LocalName) Then
productcount += 1
End If
End While
End Using
Response.Write(String.Format("found {0} products!", productcount))

Using bulk
bulk.DestinationTableName = "Product"

Dim xd As New System.Xml.XmlDocument()
xd.Load("C:Documents and SettingsSimonMy DocumentsVisual Studio 2005WebSitesWebSite1App_Dataxmlfile.xml")
Dim xr As New System.Xml.XmlTextReader(Server.MapPath("~/App_Data/xmlfile.xml"))

bulk.ColumnMappings.Add("TDProductId", "TDProductId") ' map First to first_name

bulk.ColumnMappings.Add("name", "name") ' map Last to last_name

bulk.ColumnMappings.Add("description", "description") ' map Date to first_sale

bulk.ColumnMappings.Add("imageUrl", "imageUrl") ' map Amount to sale_amount

bulk.ColumnMappings.Add("productUrl", "productUrl") ' map UserID from Session to user_id

bulk.ColumnMappings.Add("price", "price")
bulk.ColumnMappings.Add("currency", "currency")
bulk.DestinationTableName = "Affilaite_Product_new"

Try
bulk.WriteToServer(xd)
Catch ex As Exception
Response.Write(ex.Message)
Finally
myNewConnection.Close()
bulk.Close()
xd = Nothing

End Try

End Using


End Sub