XML Index

IF OBJECT_ID('T') IS NOT NULL

      DROP TABLE T

 

CREATE TABLE T ( Col1 INT PRIMARY KEY, XmlCol XML )

GO

 

-- Create primary index

CREATE PRIMARY XML INDEX PIdx_T_XmlCol ON
T(XmlCol)

GO

 

-- Create secondary indexes (PATH, VALUE, PROPERTY)

CREATE XML INDEX
PIdx_T_XmlCol_PATH ON T(XmlCol) 

      USING XML INDEX PIdx_T_XmlCol FOR
PATH

GO

 

CREATE XML INDEX
PIdx_T_XmlCol_VALUE ON T(XmlCol)

      USING XML INDEX PIdx_T_XmlCol FOR
VALUE

GO

 

CREATE XML INDEX
PIdx_T_XmlCol_PROPERTY ON T(XmlCol)

      USING XML INDEX PIdx_T_XmlCol FOR
PROPERTY

GO

 

SELECT * FROM sys.xml_indexes WHERE
object_id = object_id('T')

 

INSERT INTO T VALUES (1,

'<doc
id="123">

<sections>

<section
num="2">

<heading>Background</heading>

</section>

<section
num="3">

<heading>Sort</heading>

</section>

<section
num="4">

<heading>Search</heading>

</section>

</sections>

</doc>')

GO

 

SELECT * FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, NULL, 'DETAILED')

GO

 

DECLARE @index_id int

SELECT  @index_id = i.index_id

FROM    sys.xml_indexes
i 

WHERE   i.name = 'PIdx_T_XmlCol' and object_name(i.object_id) = 'T'

 

SELECT * FROM
sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')

SELECT * FROM
sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')

GO

 

SELECT i.name, object_name(i.object_id), stats.* 

FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, DEFAULT, 'DETAILED') stats

            JOIN
sys.xml_indexes i ON
(stats.object_id = i.object_id and stats.index_id = i.index_id)

WHERE secondary_type is
not null

GO

 

DROP INDEX PIdx_T_XmlCol_PATH ON
T 

DROP INDEX PIdx_T_XmlCol_VALUE ON
T

DROP INDEX PIdx_T_XmlCol_PROPERTY ON
T

 

-- drop primary index

DROP INDEX PIdx_T_XmlCol ON
T

 

-- drop table T

DROP TABLE T

 

IF OBJECT_ID('T') IS NOT NULL

      DROP TABLE T