Jeff Moden (7/21/2014)
Evil Kraig F (7/21/2014)
mister.magoo (7/21/2014)
So is that right? You have a stored procedure that produces XML from data stored in tables and now you want to shred that XML to store the data in table(s)?The question does arise: Why not just pop that data into the table in the first place?
I do this pretty regularly Magoo. It saves me circular trips between linked servers to pass over a reference or restriction table in XML, shred it on the far side, let it use it as a JOIN predicate list, and then get a restricted set of results back.
If I could use the table parameter between linked servers, I wouldn't have to jump the hoops.
Side Note: I'll wait for Eirikur to come back on this, my work with .nodes isn't that spectacular. I'm more familiar with OPENXML methods.
I have to admit that using XML to do such a thing (and, yes, I have send data "over" for the very same reason you've suggested) would be the furthest thing from my mind except for maybe sending it as EDI. 😛
I think XML is a brilliant way of passing multi value parameters, the binary XML format is very efficient, it can hold almost any data structure, with a schema collection it can have all the constrictions and control of a relational schema, value lookup and validation etc.. Any other approach is either less capable or more difficult to implement for but the simplest of applications.
😎