In my previous post, I mentioned that we had an issue with an XML column within a table from a vendor we use.
From the vendors point of view - each customer would probably need different data that they would want as metadata stored for a document in their database, and so using an XML structure probably made sense.
In the query issue we had there was a "cross apply" used for the XML, and it left an assumption that any reader just knew what that meant and why a "Cross Apply" was even used here.
A number of years ago - I was attending a software conference and someone told me that they knew some people who were going to put up a new website for question and answers for technical questions, that was to be called Stackoverflow; I was an early adopter and have mentioned it to anyone learning software development. For this subject of "cross apply" there is a good answer of "why cross apply is needed when using xpath queries". The .nodes() function returns a rowset - which the FROM clause needs - and the XPATH can shorten the amount of XPath type names needed in the select.
It is that additional rowset query part - that made the vendor query seem like a table scan within a table scan and was performing so badly.
The previous post calls out the user defined function to extract out a single node into a computed column; and then putting an index on that column. Taking the query time from 4.5 minutes to subsecond response time. As the computed column eliminated the need for any cross apply - and a subquery to a an XML column's rowset.