Last post Oct 01, 2010 02:05 PM by Jay Akhawri
Sep 28, 2010 10:38 AM|evvo|LINK
i have a project which will receive a large XML file from client via upload to web application, file about 0.5 gb (200,000 records), and i need to check the data and add to database any NEW records (unfortunately i wont get an incremental update file, it
will include all data previously added).
i will likely do this using a web service but wondered what other options i had. i was considering if sql server had capabilities to do this, above what i intended with having a stored procedure take the parsed data from my asp.net app.
Considerations are speed of processing and simplicity of database updating.
also is there a way to "kick off" this web service/app when the file is uploaded by the client? Or is it best/easier to just include the database processing in same module as does the save to server directory?
im likely going to archive each file after data been stored for a short time before deleting.
Oct 01, 2010 02:05 PM|Jay Akhawri|LINK
I see the issue can be broken down to the following
a) recieve the XML and identify NEW records.
- Requires parsing and possibly transformation (XSLT). LINQ to XML may come handy.
Note: Once you have identified new records you have choice to produce the new rows in a new XML or to a new CSV/Text format as the output of the XSLT Transformation.
b) Insert new records to SQL Server.
- Can be done using Microsoft SQLXML Bulkload (if output format of NEW records are XML).
Note: In Web Service/ASP.NET scenario keep in mind that you might need to consider STA Threading issues. This article may help
- Can be doen using SqlBulkCopy or SSIS (if output format of NEW records are Text/CSV)
c) Kick off the app which performs a) and b)
I hope this helps.