CODEWITHSUNDEEP
asp.netxmlsql-server-2008
PeteTheGreek
PeteTheGreek

Reputation: 729

Bulk import / update of records from xml, SQL Server 2008

Whats the best method of going about updating 10000's of rows in a SQL Server 2008 database from an xml feed? It's a .net web application with a large customer data database, I'd like it do the update from the application and not have to mess about with sql manager etc.

I notice there's a few similar questions, with a popular answer being BULKCOPY the xml data into a temp table, then do an UPDATE onto the existing table?

Thanks

Upvotes: 1

Views: 795

Answers (1)

marc_s
marc_s

Reputation: 754528

Not knowing what exactly your XML is and what it looks like, nor not knowing what your tables look like that you want to update, I'm speculating here....

You basically have two options: get your XML feed into SQL Server and handle shredding that XML data into relational data there (works quite well with the XQuery support), but you seem to prefer doing this on the client, in your .NET application.

In that case, I would probably do something like this (not knowing any details about your scenario, this has to be rather vague....):

  • grab the list of interesting nodes with the useful data from the XML feed
  • define a DataTable with all the columns that you want to store/handle/use for your updates
  • turn each XML node into a row in that in-memory DataTable and fill in the values
  • when you're done with iterating over the XML nodes, bulk insert the DataTable into a staging table in your SQL Server
  • have a SQL statement or stored procedure update your original customer data from that data in the staging table

Upvotes: 2

Related Questions