I have to search for a Postcode in my XML type column. I have created a primary XML index on my table.
I am following this article to understand the concepts.
My query looks like this
select *some fields* from table_name where
[xml_column].exist(''/Session/Entries/Entry[@DataItemId="Address.Postcode" and contains(upper-case(.), "@Postcode")]'') = 1
@Postcode is the input parameter and user can enter full or partial postcode
Now according to the link,
If your workload uses path expressions significantly on XML columns, the PATH secondary XML index is likely to speed up your workload. The most common case is the use of the exist() method on XML columns in the WHERE clause of Transact-SQL.
But then I get little confused with the following statement
If your workload retrieves multiple values from individual XML instances by using path expressions, clustering paths within each XML instance in the PROPERTY index may be helpful. This scenario typically occurs in a property bag scenario when properties of an object are fetched and its primary key value is known.
I am using exist()
method in my where
clause but I am returning multiple records.
I am confused as to which secondary index should I create as per my query. I am inclining more towards the PROPERTY index.
Can I create all 3 and let SQL handle which one to choose ?? Will this increase the size of the node table and hamper the speed of the database ??
Part of my XML looks like this
<Session>
<Entries>
<Entry DataItemId="AccountNumber" Type="Pattern" Source="SessionStore">
<Value>212312311</Value>
</Entry>
<Entry DataItemId="SortCode" Type="Pattern" Source="SessionStore">
<Value>10-20-30</Value>
</Entry>
<Entry DataItemId="AccountName" Type="Free" Source="SessionStore">
<Value>XXXXXXXXX</Value>
</Entry>
<Entry DataItemId="Let" Type="Set" Source="SessionStore">
<Value>LET</Value>
</Entry>
<Entry DataItemId="Relative" Type="Boolean" Source="SessionStore">
<Value>False</Value>
</Entry>
<Entry DataItemId="LtdCompany" Type="Boolean" Source="SessionStore">
<Value>False</Value>
</Entry>
<Entry DataItemId="Address.Postcode" Type="Pattern" Source="SessionStore">
<Value>GL8 1TT</Value>
</Entry>
<Entry DataItemId="Address.Line1" Type="Free" Source="SessionStore">
<Value>Askdaskdaoiksd</Value>
</Entry>
<Entry DataItemId="Address.Line2" Type="Free" Source="SessionStore">
<Value>Ojasuiodjaisjdsa</Value>
</Entry>
<Entry DataItemId="PropertyType" Type="Set" Source="SessionStore">
<Value>DH</Value>
</Entry>
<Entry DataItemId="Tenure" Type="Set" Source="SessionStore">
<Value>1</Value>
</Entry>
<Entry DataItemId="BuiltThisYear" Type="Boolean" Source="SessionStore">
<Value>False</Value>
</Entry>
<Entry DataItemId="YearBuilt" Type="Integer" Source="SessionStore">
<Value>1960</Value>
</Entry>
<Entry DataItemId="StdConstruction" Type="Boolean" Source="SessionStore">
</Entries>
</Session>
UPDATE
I did some more digging and then had a chat with senior DBA friend of mine and who advised me to created a PATH index. The query seems to be working way quicker than before.