Thank you very much for taking the time to reply. I apologize for not being more clear, but I was looking for a solution using SSIS and Data Flow tasks - I have figured out how to do this using a Merge/Join Transformation. If anyone needs to do it this way let me know and I will post what I have come up with.
However, the information that you have provided will be helpful as well - it will serve as a good reference for using the SQL nodes function. I have not done much development with regard to querying XML data this way, and always find myself having to research it again any time that it comes up.