How to upload Dataset read from XML to SQL Server Database

[Neo]

Hello All,

I am using SQL Server 2000 and .Net 2.0. I want to import XML files
into database. SQLXMLBulkLoad is useless since, our schema doesn't have
annotation required by that.

But I could read schema and xml file in dataset without problem. Now
only quesiton is how to insert this dataset in database. One way is
insert each record from dataset at a time, which I think is pretty
crude way. Is there any faster way to do bulk insert of dataset loaded
from xml into database?

Best Regards,
Pravin

 

[Cowboy \(Gregory A. Beamer\)]

From a developer standpoint, you can set up a DataAdapter and call update,
but that is still record by record behind the scenes, at least AFAIK.

You could send the DataSet XML into the server as a string and have it
insert using OPENXML, but I have not been able to get this to work properly
in SQL 2000 without stripping the xmlns attribute from the root tag, which
requires working with it as a string. Once the attribute is gone, everything
is fine. Note that this reduces the amount of info you can load at a time,
unless you move to a text type for input, which can be problematic. For
large XML files, you may have to chunk.

While very outside of the box, BizTalk is another option, but an expensive
one if this is the only purpose for it.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

 

[Cor Ligthert [MVP]]

Pravin,

The first thing you have to think about is forever the rowstate of of the
rows used in the dataset/datatable. Withough affecting those everything is
useless that you do.

http://msdn2.microsoft.com/en-us/library/system.data.datarow.rowstate.aspx

I hope this helps,

Cor