Sitecore Rocks Query Analyzer Ingredients for the Sitecore ASP.NET CMS

This blog post is not a complete cookbook, but attempts to provide some of ingredients for constructing queries in Sitecore Rocks, the Visual Studio extension for developers working with the Sitecore ASP.NET web Content Management System (CMS). This post is very short today, but over time I intend to add common queries and query concepts.

You can access Sitecore Rocks Query Analyzer in a number of ways. To query a Sitecore database, you can right-click on the database in Sitecore Explorer, then click Tools, and then click Query Analyzer.

First, remember that Sitecore Query and Sitecore Rocks Query Analyzer use very different syntaxes.

Second, for developers familiar with SQL, one important thing to remember is that Sitecore query uses XPath predicates rather than where clauses. So this attempt to determine the ID of a rendering is invalid:

select @@path, @@id from /sitecore/layout/renderings//* where @@key = 'sample rendering'

This works:

select @@path, @@id from /sitecore/layout/renderings//*[@@key = 'sample rendering']

Once you have the ID of the rendering, to identify all items that contain layout details that use that rendering:

select @@path from //*[contains(@__Renderings, '{493B3A83-0FA7-4484-8FC9-4680991CF743}')]

To find the value of the Source property of all data template fields of type Rules:

select @@path, @Source from /sitecore/templates//*[@Type='rules']

I can never remember the syntax for macros in text fields of condition and action definition items for the Rules engine. To get some examples (select the path of the item and the value of the Text field from all items under the specified path that contain "tree" or "Tree" in the field named Text):

select @@path, @text from /sitecore/system/Settings/Rules//*[contains(@text, 'tree') or contains(@text, 'Tree')]

Determine the Source property (the URL to load) for all data template fields of type iframe in all data templates (where the names of the field types, such as IFrame, come from under the /sitecore/system/Field types item in the Core database):

select @@path, @Source from /sitecore/templates//*[@@templatekey = 'template field' and @type='IFrame']

Determine the number of data template fields of type iframe:

select count(/sitecore/templates//*[@@templatekey = 'template field' and @type='IFrame']) as #Number of IFrame Fields#

Determine sublayout definition items with a value in the Datasource Location field:

select @@path, @#Datasource Location# 
from /sitecore/layout/Sublayouts//*[@@templatekey = 'sublayout' and @#Datasource Location# != '']
order by #Datasource Location#

Determine data template fields with any value in the Source property:

select @@path, @Source 
from /sitecore/templates//*[@@templatekey = 'template field' and @Source != '']
order by Source

Determine data template fields that use queries in the Source property:

select @@path, @Source
from /sitecore/templates//*[@@templatekey = 'template field' and contains(@source, 'query:')] 
order by Source

Determine all Rich Text Editor fields with an HTML editor profile specified in the Source property:

select @@path, @Source 
from /sitecore/templates//*[@@templatekey = 'template field' and @type = 'Rich Text' and contains(@Source, '/sitecore/system/Settings/Html Editor Profiles')]
order by Source

To select content items last updated after a given date:

select @@path, @__Updated as Updated, @#__Updated by# as #Updated by
from /sitecore/content//*[@__updated > '20121001T00:00:00'] order by Path

You can use the use keyword to specify a database. For example, to retrieve all items in the Core database that contain a value in a field named Assembly:

use core;
select @@Path, @Assembly from //*[@Assembly != '']