Thursday, July 05, 2012 5:23 PM
I know there are various methods to load a XML file into SQL Server. From my research on the web, people's opinions vary greatly so I am hoping someone can shed some light on their opinion on what would be the best approach for my given situation.
I need to create a generic load procedure that will take an unknown XML file and load it into a SQL table. (ftr - I am using SQL Server 2008). The columns are unknown and can change from file to file. The XML file can be small or it can be large. Pretty much the XML file can be anything as we have many different clients and my procedure/package needs to be able to accomodate it.
I have played with using OPENROWSET, OPENXML and in SSIS - Scripts, XML Source. I can get all methods to work (with limitations (such as knowing the root, etc)) but I didn't want to paint myself into a corner. I wanted to implement the most flexible and robust solution for my situation.
If anyone can shed some light on this I would greatly appreciate it.
Thanks in advance.
Thursday, July 05, 2012 5:27 PMModerator
You really dont need to know anything about the XML to bulk load it. You can use openrowset with the bulk keyword and single_blog. Another option is to use bulk import with a format file (make sure the column is set to binary in the format file)
Friday, July 06, 2012 1:33 PMThank you Adam!