Retrieving List Item using CAML Query against Taxonomy Field in SharePoint 2013

Sathish Nadarajan
 
Solution Architect
May 14, 2015
 
Rate this article
 
Views
33529

In the last article, we saw some interesting facts about the taxonomy field. In this, article, let us focus how to do a CAML Query execution against the Taxonomy field and some more interesting information.

As I mentioned earlier, Taxonomy Hidden List will be created for every Site Collection. i.e., Taxonomy Hidden List is a Site Collection Level List.

The List will be as below.

image

As this is a Hidden List, type the URL and navigate to this List. We cannot see a link on the Site Contents to this list. The URL would be something like http://SiteCollectionURL/Lists/TaxonomyHiddenList

You can see the fields Title, ID, IdForTerm, IdForTermSet etc.,

Here the IdForTerm, IdForTermSet, Title will be same for the Terms across site collection. The “ID” Column is specific to the Site Collection. i.e., For Example, I have a Term called “Company Information”. I have two Site Collection which uses this Term. Then, the Term “Company Information” will be available on both the “Taxonomy Hidden List” of the two site collections. But on the two lists, the rest of the column values will be the same. But not the ID.

This ID is the Key factor which we will be using for our CAML Query.

Basically, why we are doing this exercise is, we cannot execute a CAML Query based on the ID of the Term. (We can execute a KeyWordSearchQuery by using the ID. Probably later sometime, we can see about that). Either we can do a Query by Term Name or the WSSID. Yes, this WSSID is the one which we are discussing from the TaxonomyHiddenList.

Out of this two methods, i.e.,

1. Query against Term Name

2. Query Against WSSID

The first one will give some negative results. Like, there can be a situation like the Term Name can be same under different Term Group. In this case, we cannot rely on the Term Name execution. Moreover all of us would be aware that always Querying against the ID would be most optimum rather than Term Name.

Anyhow, we will see both the methods, how to do the query.

1. Query against Term Name

 public TaxonomyFieldValueCollection GetNavigationTags (string authoringSiteURL, string tagName)
         {
              
             TaxonomyFieldValueCollection taxonomyFieldValueCollection = new TaxonomyFieldValueCollection(string.Empty);
 
             try
             {
                 using (SPSite site = new SPSite(authoringSiteURL))
                 {
                     SPWeb web = site.RootWeb;
                     SPList list = web.Lists[MYLIST];
 
                     SPQuery query = new SPQuery();
                     query.Query = @"<Where>
                                             <Contains>
                                                 <FieldRef Name='" + MyTaxonomyFieldName + @"' />
                                                 <Value Type='Text'>" + tagName + @"</Value>
                                             </Contains>
                                         </Where>";
                     query.RowLimit = 1;
 
                     SPListItemCollection itemcollection = list.GetItems(query);
                      
 if (itemcollection != null)
                     {
                         if (itemcollection.Count > 0)
                         {
                             SPListItem item = itemcollection[0];
                             string strNavigationTag = Convert.ToString(item[MyOtherTaxonomyField]);
                             taxonomyFieldValueCollection.PopulateFromLabelGuidPairs(strNavigationTag);
                         }
                     }
                 }
             }
             catch (Exception ex)
             {
                    
                 taxonomyFieldValueCollection = null;
             }
             return taxonomyFieldValueCollection;
         }
 

The sample code, I have taken it from my previous requirement. Hence, I request the Users to modify according to their requirement. Here, I was about the focus only on the CAML Query alone.

If you closely look at the CAML Query, while executing against the Name, we can do only a “Contains” Operation. We cannot execute a Equals Operations over here.

Hence, the result may not be accurate as we expect.

2. Query Against WSSID

To get the more accurate result, we need to modify the code as below.

 public TaxonomyFieldValueCollection GetNavigationTagsFromTagMapping(string authoringSiteURL, string tagLookupID)
         {
              
             TaxonomyFieldValueCollection taxonomyFieldValueCollection = new TaxonomyFieldValueCollection(string.Empty);
 
             try
             {
                 using (SPSite site = new SPSite(authoringSiteURL))
                 {
                     SPWeb web = site.RootWeb;
                     SPList list = web.Lists[MyList];
 
                     SPQuery query = new SPQuery();
                     query.Query = @"<Where>
                                         <In>
                                             <FieldRef LookupId='TRUE' Name='" + MyTaxonomyField + @"' />
                                             <Values>
 	                                            <Value Type='Integer'>" + tagLookupID + @"</Value>
                                             </Values>
                                         </In>
                                 </Where>";
                     query.RowLimit = 1;
 
                     SPListItemCollection itemcollection = list.GetItems(query);
                     if (itemcollection != null)
                     {
                         if (itemcollection.Count > 0)
                         {
                             SPListItem item = itemcollection[0];
                             string strNavigationTag = Convert.ToString(item[MyOtherTaxonomyField]);
                             taxonomyFieldValueCollection.PopulateFromLabelGuidPairs(strNavigationTag);
                         }
                     }
                 }
             }
             catch (Exception ex)
             {
                  
                 taxonomyFieldValueCollection = null;
             }
             return taxonomyFieldValueCollection;
         }
 
 Now, the above code will give back only the precise results. 
 Again, the question would be, how to get the TagLookUPID of a Term.
 This TagLookUPID is the ID from the TaxonomyHiddenList and can also be referred as WSSID.
 We cannot do a query against TaxonomyHiddenList to get this ID.  There is another way to retrieve this.  Because, as Taxonomy Hidden List may not be accessible by all the users and in case any permission issue, we will face some run time exception for some users.
 We can directly get the WSSID from Term by using the below code.
 
 public int GetWSSID(string authoringSiteURL, string ManagedMetadataServiceApplicationName, string tag)
         {
              
             int returnValue = 0;
             using (SPSite authoringSite = new SPSite(authoringSiteURL))
             {
                 TaxonomySession session = new TaxonomySession(authoringSite);
                 TermStore mainTermStore = session.TermStores[ManagedMetadataServiceApplicationName];
                 //string tag = "Company Information|fe4cf22c-168b-425c-ba82-589a56ca9fdb";
                 TaxonomyFieldValue tax = new TaxonomyFieldValue(string.Empty);
                 tax.PopulateFromLabelGuidPair(tag);
                 Term foundTerm = session.GetTerm(new Guid(tax.TermGuid));
                 int[] wssIds = TaxonomyField.GetWssIdsOfTerm(authoringSite, mainTermStore.Id, foundTerm.TermSet.Id, foundTerm.Id, false, 1);
 
                  
 
                 if (wssIds.Length > 0)
                 {
                     returnValue = wssIds[0];
                 }
 
             }
              
             return returnValue;
         }
 

Now, the above code will give back only the precise results.

Again, the question would be, how to get the TagLookUPID of a Term.

This TagLookUPID is the ID from the TaxonomyHiddenList and can also be referred as WSSID.

We cannot do a query against TaxonomyHiddenList to get this ID. There is another way to retrieve this. Because, as Taxonomy Hidden List may not be accessible by all the users and in case any permission issue, we will face some run time exception for some users.

We can directly get the WSSID from Term by using the below code.

 public int GetWSSID(string authoringSiteURL, string ManagedMetadataServiceApplicationName, string tag)
         {
              
             int returnValue = 0;
             using (SPSite authoringSite = new SPSite(authoringSiteURL))
             {
                 TaxonomySession session = new TaxonomySession(authoringSite);
                 TermStore mainTermStore = session.TermStores[ManagedMetadataServiceApplicationName];
                 //string tag = "Company Information|fe4cf22c-168b-425c-ba82-589a56ca9fdb";
                 TaxonomyFieldValue tax = new TaxonomyFieldValue(string.Empty);
                 tax.PopulateFromLabelGuidPair(tag);
                 Term foundTerm = session.GetTerm(new Guid(tax.TermGuid));
                 int[] wssIds = TaxonomyField.GetWssIdsOfTerm(authoringSite, mainTermStore.Id, foundTerm.TermSet.Id, foundTerm.Id, false, 1);
 
                  
 
                 if (wssIds.Length > 0)
                 {
                     returnValue = wssIds[0];
                 }
 
             }
              
             return returnValue;
         }
 

In the above method, if we see closely the one line which we expect is

 int[] wssIds = TaxonomyField.GetWssIdsOfTerm(authoringSite, mainTermStore.Id, foundTerm.TermSet.Id, foundTerm.Id, false, 1);
 

To pass the parameters, TermStoreID, TermSetID, and TermID, we require the other lines of code.

By this, we can get the WSSID and by using the WSSID, we can do a perfect CAML Query Execution against the Taxonomy Field.

Though it is rarely used functionality, it is very essential whenever we are facing this kind of requirement.

Happy Coding,

Sathish Nadarajan.

Author Info

Sathish Nadarajan
 
Solution Architect
 
Rate this article
 
Sathish is a Microsoft MVP for SharePoint (Office Servers and Services) having 15+ years of experience in Microsoft Technologies. He holds a Masters Degree in Computer Aided Design and Business ...read more
 

Leave a comment