Best and quickest approach to importing thousands of 2 meg XML files into XMl column?

Hi all,
I intend to use SQL Server 2005 (which I haven't used before), to store hundreds of thousands of XML files this autumn and I'm trying to figure out which approach is the best to do this. These files are very complex (multiple nested elements) and use multiple namespaces (imports).The files will be on the same server as the Database. Below is a list of some ideas I have.

1) Use Some DTS process to import the XML files if possible in SQL Server 2005?
2) Create a stored procedure that interates through a local directory, opens each XML file and inserts it's content into the database column using the bulkload method (any examples would be appreciated). Does this require the use of some scripting code such as VB script?
3) Run a server-side script such as PHP that loops through a local directory and stores the content of the XML file into a variable which is passed to a Stored procedure which stores the variable in to the database column? I have tried this and it seems very unstable and slow, roughly 2 minutes per file (the application server and database servers are on different boxes). I'm worried when I need to loop through thousands of files it will crash the servers!

Any suggestions would be appreciated

Muhi

Muhi,

SQL Server provides a command line tool called 'BCP' which is one of the good ways to bulk load XML data into an XML column. For instance you can store your XML instances in a file with a delimiter inbetween each instance (default delimiter is ,) and you can use the following command to bulk load your data:

bcp YourDB..YourTable in "XMLData.csv" -T -b 300 -N -h "TABLOCK"

This will insert the instances from 'XMLData.csv' into 'YourTable' in database 'YourDB'. BOL has a lot more information about the command line options in BCP. There are also more hints you can provide to BCP to improve performance. For instance if you have a clustered index on the table and if you know that your input data in the file is ordered on the index column (say id) then you can provide another hint to BCP -h "ORDER(id)" to avoid some additional sorts.

Thanks

Babu

|||

Hi Muhi,

try SQL Server 2005 Books Online

Examples of Bulk Importing and Exporting XML Documents

http://msdn2.microsoft.com/en-us/library/ms191184.aspx

Best regards

Jiri