Grant Fritchey (9/4/2013)
Here's a question. Will more than one user be running this query? If so, using a permanent table will require you to also have a mechanism to separate out each person's data so that you're not stepping on each other.
That why I mentioned I needed to add a SessionId column, to filter for each user that uses the SP. The SP can be used 10 or more times at the same time and over 400 times a day (it's the SP for adding orders and recalculating stocks).
Grant Fritchey (9/4/2013)
In general, when doing this kind of work, assuming the secondary processing needs statistics (meaning, you filter on the data after loading it) then I would use temporary tables. If you don't need statistics (no filtering of ANY kind including JOIN operations), then I would use table variables. But then, if you don't need to do secondary processing, I'd just use XQUERY to access the XML directly.
The data is used more than one in the "main" SP and in the other SPs as well, that's why we're using temp tables. Isn't it faster to store the data in a table rather than using .nodes() over and over again or sp_xml_preparedocument? In the past I also made a test comparing nodes and sp_xml_preparedocument: there's no big difference in performance (time) but if the XML uses attributes and there are over 50 or so the nodes is a lot more slower.
Also the data is filtered, used to update some other table data.
Thanks,
Pedro