I came across this really simple best practice from BOL for the xml method
exist which when used will give you a much cleaner and faster query plan. If like me you commonly use the name of an element or attribute as part of an XQuery predicate, then you would benefit immediately from this.
So lets create a table with a single blob of xml and try out two queries to show this in action. The full script can be downloaded from here
--Create example table CREATE TABLE XmlTable ( Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, MyXml XML NOT NULL ) --Populate table with an xml blob DECLARE @XmlData XML SET @XmlData = (SELECT * FROM sys.tables FOR XML PATH('Table'), ROOT('Tables')) INSERT INTO XmlTable (MyXml) VALUES (@XmlData) GO
Query 1: Using the name of an element as part of the predicate
SELECT * FROM XmlTable WHERE MyXml.exist('/Tables/Table[name = "XmlTable"]') = 1 GO
Returns a single record and the execution plan looks like this
Query 2: Using a dot as part of the predicate
SELECT * FROM Xmltable WHERE MyXml.exist('/Tables/Table/name[. = "XmlTable"]') = 1 GO
Also returns a single record but the execution plan is simpler and looks like this
Here is the record that is returned in both queries
As you can see, both queries return the exact same result (single record) because they are identical queries. However because of the way the first query has to work, it incurs this additional evaluation step which is reflected in the plan. The BOL explanation for this is:
Although the result is the same the first form usually requires one more evaluation step. This is because the query processor is evaluating only one node in the second form (using the PATH index if its present) and is using two evaluation steps (one for /Tables/Table, one for /Tables/Table/name) in the first form. Although looking at the plan for two "equivalent" queries might seem strange for XML aficionados, SQL query tuners have been doing this for years. Note that the two queries above only produce the same results when using the XML data type exist method, they produce different results when used with the query method.