Sitecore Blog: @sitecorejohn blog

Sitecore Rocks Queries

By John West, July 02, 2011 | Rating:  | Comments (11)

Sitecore logo over rocks

This blog post provides information about invoking queries with Sitecore Rocks, the Visual Studio extension for developers working with the ASP.NET CMS. Sitecore Rocks Query Analyzer allows you to identify, publish, update (including search and replace), package, serialize, import, delete and otherwise manipulate items from within Visual Studio. For more information about Sitecore Rocks, see my blog post Introducing Sitecore Rocks.

Sitecore Rocks provides a Query Analyzer application that does more than just evaluate Sitecore queries. Query Analyzer uses a scripting syntax based on Sitecore Query, which in turn is based on XPath. For more information about Sitecore Query, see Using Sitecore Query on the Sitecore Developer Network (SDN).

Unlike queries that you can run in the Sitecore CMS, Query Analyzer supports deletes, updates and inserts (all four CRUD functions). As this application actually invokes queries as opposed to analyzing them, the name may change, but like SQL (Structured "Query" language) itself, it might not.

To access Query Analyzer in Visual Studio, in Sitecore Explorer, right-click a database, and then click Query Analyzer. Query Analyzer opens with a default query of "help;"; click Execute to get a list of available commands. Enter "help <command>;" to get information about a specific command. Available commands include:

  • create: Create a data template
  • delete: Delete one or more items
  • help: Display a list of query keywords
  • import: Import items (such as from a .csv file - for more information, see
  • insert: Insert one or more items
  • package: Create a Sitecore package containing selected items
  • publish: Publish one or more items
  • replace: Search and replace strings in field values
  • select: List items
  • serialize: Serialize items (for more information about serialization, see the Serialization Guide on SDN)
  • set: Set query parameters including the context node and the maximum number of items to process
  • update: Update one or more fields in one or more items
  • use: Invoke subsequent queries against a specific database

Another way to open Query Analyzer is to right-click an item (or the header in item editor), then click Tools, then click Scripts, and then click Select, Update, Insert, Delete or Publish. This opens Query Analyzer with a stub query to which you can add, which is a great way to learn the syntax.

There is yet another way to construct a query, specifically for search and replace:

  1. Right-click a database and then click Search and Replace. The Search and Replace dialog appears.
  2. In the Search and Replace dialog, enter parameters such as the text to replace, the text with which to replace that text, the field on which to operate (optional) and the root item for the update statement.
  3. In the Search and Replace dialog, click the Script tab, and copy the contents to a Query Analyzer window.

Of course, Sitecore Query does exactly what you tell it to do, which can be dangerous. Use caution around keywords that update items.

Tags: Architecture, Infrastructure


  • contains some additional details and an example query

    - John West
    July 15, 2011 at 6:25 AM

  • Sitecore Rocks Query Examples:

    - John West
    May 08, 2012 at 12:21 PM

  • Publish to specific publishing targets ( publish Internet, Extranet from /sitecore/content/Home About publishing targets: Sitecore query cheet sheet:

    - John West
    May 13, 2012 at 6:45 AM


    - John West
    September 21, 2012 at 8:56 AM

  • John, do you know if there is a way to determine what publication restrictions are in place using rocks query analyzer and also to update this? For example, where one of the publicaton targets is unchecked. Thanks, Chris.

    - Chris Lewis
    February 06, 2013 at 12:51 AM

  • In a test environment...check this: select @@Path, @#__Publishing groups# from //*[@#__Publishing groups# != '']; Assuming you have a single publishing target in addition to the default: select @@Path, @#__Publishing groups# from //*[@#__Publishing groups# != '' and not(contains(@#__Publishing groups#, '{NEW_PUBLISHING_TARGET}'))]; Based on help update, this is my guess, but it seems to clear the field instead of appending - maybe Sitecore doesn't parse concat() in this context. update set @#__Publishing groups# = concat(@#__Publishing groups#, '|{NEW_PUBLISHING_TARGET}') from //*[@#__Publishing groups# != '' and not(contains(@#__Publishing groups#, '{NEW_PUBLISHING_TARGET}'))];

    - John West
    February 06, 2013 at 7:54 AM

  • One alternative may be to hard-code a list, but that might require additional criteria in the predicate. For example: update set @#__Publishing groups# = '{DEFAULT_PUBLISHING_TARGET}|{NEW_PUBLISHING_TARGET}' from //*[@#__Publishing groups# != '' and not(contains(@#__Publishing groups#, '{NEW_PUBLISHING_TARGET}'))]; Or for example if you have a third publishing target, maybe something like: update set @#__Publishing groups# = '{DEFAULT_PUBLISHING_TARGET}|{NEW_PUBLISHING_TARGET}' from //*[contains(@#__Publishing groups#, '{DEFAULT_PUBLISHING_TARGET}') and not(contains(@#__Publishing groups#, '{NEW_PUBLISHING_TARGET}' and not(contains(@#__Publishing groups#, '{{THIRD_PUBLISHING_TARGET}}'))]; update set @#__Publishing groups# = '{DEFAULT_PUBLISHING_TARGET}|{NEW_PUBLISHING_TARGET}}|{THIRD_PUBLISHING_TARGET}' from //*[contains(@#__Publishing groups#, '{DEFAULT_PUBLISHING_TARGET}') and not(contains(@#__Publishing groups#, '{NEW_PUBLISHING_TARGET}')]; Note that in this case the first update should be equivalent to the following, but I wanted to include a more complete example. update set @#__Publishing groups# = '{DEFAULT_PUBLISHING_TARGET}|{NEW_PUBLISHING_TARGET}' from //*[@#__Publishing groups# = '{DEFAULT_PUBLISHING_TARGET}'];

    - John West
    February 06, 2013 at 7:55 AM

  • A better update (use + instead of concat()): update set @#__Publishing groups# = @#__Publishing groups# + '|{6FE21C51-FBD6-42D4-82DF-900B1CF796A7}' from //*[@#__Publishing groups# != '' and not(contains(@#__Publishing groups#, '{6FE21C51-FBD6-42D4-82DF-900B1CF796A7}'))];

    - John West
    February 06, 2013 at 9:42 AM

  • As usual John, you are truly amazing.

    - Chris Lewis
    February 06, 2013 at 10:55 PM

  • Hi Chris and John, I experience also more time outs now that i try more complex queries. Our sitecore installation is pretty big (several websites in same instance). I try to reduce conditions or target one node at a time but sometimes it is still not enough and Rocks times out. Did you get any feedback from Jakob? best, Pierre

    - Pierre Sapinault
    September 15, 2014 at 10:35 AM

  • Pierre, no, I've tended to not use Sitecore Rocks Queries because of the upper limit on the resultsets returned. Much of what I was using it for can be easily obtained by using the Sitecore Powershell module instead ( Chris.

    - Chris Lewis
    September 16, 2014 at 4:00 AM

*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.