MSDN WebCast Demo: XML Indexing
-- Indexing of an XML datatype column
-- Execute every statement in the sample in order
-- Follow links to see estimated query plans
-- (c) 2004, 2005 Microsoft Corp.
USE
msdn2005;
GO
-- Run a query (look at estimated query plan)
SELECT doc
FROM XMLdoc
WHERE 1 = doc.exist(
'declare default element namespace "urn:example/customer";
/doc/customer[order/@year>2000]
');
GO
-- Create primary XML Index
CREATE PRIMARY XML INDEX PrimXIdxXMLdoc ON XMLdoc(doc);
GO
-- Run query again (look at estimated query plan again)
SELECT doc
FROM XMLdoc
WHERE 1 = doc.exist(
'declare default element namespace "urn:example/customer";
/doc/customer[order/@year>2000]
');
GO
-- Create a secondary PATH index
CREATE XML INDEX PathIdxXMLdoc ON XMLdoc(doc)
USING XML INDEX PrimXIdxXMLdoc FOR PATH;
GO
-- Run query again (look at estimated query plan again)
SELECT doc
FROM XMLdoc
WHERE 1 = doc.exist(
'declare default element namespace "urn:example/customer";
/doc/customer[order/@year>2000]
');
GO
-- Now show an even simpler plan by rewriting the query to simplify the plan
-- Look at estimated query plan
SELECT doc
FROM XMLdoc
WHERE 1 = doc.exist(
'declare default element namespace "urn:example/customer";
/doc/customer/order/@year[.>2000]
');
GO