Jeff Moden (9/19/2013)
What's the best way to store the XML?
The best way, IMHO, is to shred it on receipt and store it as properly normalized data. XML is formatted data and one of the worst data sins there is is to store formatted data.
The problem is that we have clients that have receipts with over 1000 lines, so to send the XML has proper data to the SP would mean to call and SP 1000 times or to have user defined data table (only exists in SQL 2008 and VB6 doesn't support it... this is our big problem).
That why we decided to use the XML to send the data to the SP and then create the temp table with its data to be used in all the process (we are code reviewing the process and so far found over 50 SP, FN and TRG that are called "along" the way...).
If you need to work better, try working less...