drop table if exists [dbo].[tbl_XML1], [dbo].[tbl_XML2]
create table [dbo].[tbl_XML1] ([RID] BIGINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
                              [Date] DATETIME,
                              [XmlData] XML);

go
create selective xml index sxi_1 on [dbo].[tbl_XML1]([XmlData])
for (
    abc = '/a/b/c' as XQUERY 'node()',
    abc_val = '/a/b/c/text()' AS SQL INT
);
go

create xml index sxi_2 on [dbo].[tbl_XML1] ([XmlData])
USING XML INDEX sxi_1 for (
  abc_val
);
go

insert into [dbo].[tbl_XML1] values (getdate(), '
<root>
   <person>
      <name>Mark</name>
      <surName>Denver</surName>
      <age>30</age>
   </person>
</root>')

insert into [dbo].[tbl_XML1] values (getdate(), '
<a>
  <b id="1">
    <name>Mark</name>
    <c>30</c>
  </b>
  <val>100</val>
</a>
')


insert into [dbo].[tbl_XML1] values (getdate(), '
<a>
  <b id="2">
    <name>Adam</name>
    <c>35</c>
  </b>
  <b>
    <c id="30"/>
  </b>
  <val>100</val>
</a>
')

select object_name(o.parent_object_id) as [parent],
       o.type_desc + ' : ' + object_name(i.object_id) as [index_owner],
       i.name as [index_name],
       i.index_id,
       i.filter_definition, p.rows, i.index_id, i.has_filter, i.is_unique,
       i.type, o.schema_id, p.object_id, p.hobt_id, p.index_id, i2.name, i2.type_desc
from sys.indexes i
    left join sys.partitions p on p.object_id = i.object_id
    left join sys.objects o on o.object_id = i.object_id
    left join sys.indexes i2 on i2.object_id = p.object_id and i2.index_id = p.index_id
where i.name like 'sxi%'