Best way to solve a large XML import quandary

[chazman113]

Heres the deal, I have an XML file thats about 70 megs. I'm generally a coldfusion programmer and it of course can't handle something that big. I would like to do it natively in a stored procedure. I'm a complete noob when it comes to T-SQL programming, could someone throw me in the right direction as to how to get the following done:

* Create a stored procedure (Server is SQL Server 2005) - think I can handle this one
* Read in the XML file (on the local hard drive)
* Parse it into something I can loop over
* Run a bunch of insert statements based on this loop
* Set it up to run on a schedule every day
* Not break my server

Thanks in advance!

 

[philhege]

This is a job for SQL Server Integration Services (SSIS). It has tools that can parse XML, run SPs, etc. etc. Everything is scheduled and optimized. It's the successor to DTS packages and (since you have SQL2K5) is something you should use for this task.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[Crowley16]

also, you won't need to loop through your records, you can set it up to load as a table and do proper sql against it...

--------------------
Procrastinate Now!