Few days ago, i tried the Extended Events for replace the SQL Server profiler. Then i wanted to put the xel files generated in the sql server database with sql.
What i think odds, it's a lot of site use the function nodes with a Cross Apply to get the value in the XML, even if it's more slower than not to use it. I don't know if i am missing something?
Sample of my queries
In short: You can cut bread with a chain saw or you might use some bare wire, but don't blame the tool, if the result is not convincing (even if it's very fast :-D )...
You need .nodes() if there are 1:n related sub-nodes in order to retrieve them as a derived table.
Many people use .nodes() just to get a bit more readability into the code, especially with very deeply nested elements, when the XPath grows to some size...
You get a named current node from where you can continue with XPath navigation or XQuery. With multiple nested XML you can dive deeper and deeper using a cascade of .nodes().
If you can be sure that there will be only one element below <event> with the name <data> with an attribute @Name with the value NTCanonicalUserName there's no need for .nodes(). Assumeably the fastest would be to use something like
/event[1]/data[@name="NTCanonicalUserName"][1]/value[1]/text()[1]
Important!
If there might be more than one <event> or more than one <data> with the given attribute, your "fast" call would return the first occurance whereever it is found. This might be the wrong one... And you would never find out, that there is another one...
Your "fast" call is fast, because the engine knows in advance, that there will be exactly one result. With .nodes() the eninge will have to look for further occurances and will do the overhead to create the structures for a derived table.
General advise
If you know something, the engine cannot know, but would make the reading faster, you should help the engine...
Good to know
Internally the real / native XML data type is not stored as the string representation you see, but as a hierarchically structured tree. Dealing with XML is astonishingly fast... In your case the search is just jumping down the tree directly to the wanted element.
