Soooo, now I can handle this programatically by reading my xml data and inserting this data into the database as its read by the xml parser. But, what I'm wondering is if there is faster way of doing this. Possibly using some sort of bulk insert?
The xml data files will at times be massive. For example during the holiday season, there could be millions of records needing to be inserted. However, there will also be days when the data is small.
If you can get the data into a flat text file (fixed width columns, CRLF delimted rows) or you can write an XML parser that is significantly more efficient than the one provided by Microsoft that will produce a similar flat file, then probably.
The BCP.EXE program is orders of magnitude faster than nearly any other way to pump data into SQL Server, and the flat files described above are its format of preference. BCP.EXE can inhale data at speeds that push the theoretical limits of how fast you can get data onto disk using SQL Server.