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 senderAs Object,ByVal eAs System.EventArgs)Handles Button1.ClickDim objXMLDim objXSL' destination tableDim connectionStringAs String = ConfigurationManager.ConnectionStrings("Database1ConnectionString").ConnectionStringDim myNewConnectionAs Data.SqlClient.SqlConnection =New Data.SqlClient.SqlConnection(connectionString) myNewConnection.Open()Dim productreaderAs XmlReaderDim bulkAs Data.SqlClient.SqlBulkCopy =New Data.SqlClient.SqlBulkCopy(myNewConnection) bulk.DestinationTableName ="Product"Dim productcountAs Integer = 0Dim settingsAs New System.Xml.XmlReaderSettings() settings.IgnoreWhitespace =True settings.IgnoreComments =True Dim xsAs String = (Server.MapPath("~/App_Data/XMLfile.xml")) Using readerAs XmlReader = XmlReader.Create(xs, settings)While (reader.Read())If (reader.NodeType = XmlNodeType.ElementAnd"product" = reader.LocalName)Then productcount += 1End If End While End Using Response.Write(String.Format("found {0} products!", productcount)) Using bulk bulk.DestinationTableName ="Product"Dim xdAs New System.Xml.XmlDocument() xd.Load("C:\Documents and Settings\Simon\My Documents\Visual Studio 2005\WebSites\WebSite1\App_Data\xmlfile.xml")Dim xrAs 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 exAs Exception Response.Write(ex.Message)Finally myNewConnection.Close() bulk.Close() xd =Nothing End Try End UsingEnd Sub

That's because bulk.WriteToServer method will not accept an XmlDocument as parameter. Why not use a DataSet in this case? So you can use such code:

Dim xs As String = (Server.MapPath("~/App_Data/XMLfile.xml"))
Dim ds As New DataSet
ds.ReadXml(xs, XmlReadMode.Auto)
--...add your code
bulk.ColumnMappings.Add("TDProductId", "TDProductId")
--...
bulk.WriteToServer(ds.Tables(0))

BTW, if you're using SQL2005, you can load XML files (actually all BLOB data, including images) into SQL database by using simply OPENROWSET with SINGLE_BLOB option. You can take a look at:

http://msdn.microsoft.com/library/en-us/dnsql90/html/sql2k5xml.asp?frame=true

|||

This works a treat, many thanks for thank.

I've notice it doesn't like inserting into nulls or primary keys, but I shall look into that now.

Cheers again great advice