Sitecore Blog: @sitecorejohn blog

John West, CTO, Sitecore USA, Inc.

@sitecorejohn blog

John West, Chief Technology Officer

Sitecore Rocks Queries

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

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 http://vsplugins.sitecore.net/Sitecore-Query-Analyzer-Import.ashx)
  • 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

Comments

  • http://stackoverflow.com/questions/6697703/sitecore-rocks-query-syntax contains some additional details and an example query

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

  • Sitecore Rocks Query Examples:

    http://vsplugins.sitecore.net/Sitecore-Query-Analyzer-Samples.ashx

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

  • Publish to specific publishing targets (http://sdn.sitecore.net/Forum/ShowPost.aspx?PostID=45712):

    publish Internet, Extranet from /sitecore/content/Home

    About publishing targets:

    http://www.sitecore.net/Community/Technical-Blogs/John-West-Sitecore-Blog/Posts/2011/05/All-About-Publishing-Targets-in-the-Sitecore-ASPNET-CMS.aspx

    Sitecore query cheet sheet:

    http://www.sitecore.net/Community/Technical-Blogs/John-West-Sitecore-Blog/Posts/2012/05/Sitecore-Query-Cheat-Sheet.aspx

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

  • http://www.sitecore.net/Community/Technical-Blogs/John-West-Sitecore-Blog/Posts/2012/09/Sitecore-Rocks-Query-Analyzer-Ingredients-for-the-Sitecore-ASPNET-CMS.aspx

    - John West
    September 21, 2012 at 10: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 1: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 8: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 8: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 10:42 AM

  • As usual John, you are truly amazing.

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

  • Any ideas how to return a very large resultset for SELECTs? I have the latest version and I cant get it to return a large resultset of items (over say 1000). I've tried two things: Update Query.MaxItems setting in the Web.config to zero. Adding set maxitems = x; to the query itself (where x is an arbitrarily high value).

    NOTE - I've tried very large UPDATEs, and they work fine - I just watch them happen in the logs as they take quite a while. It's only SELECTs which I am asking about.

    - Chris Lewis
    February 27, 2013 at 10:28 AM

  • You don't get an error, but a subset of the results? If you put a count around it, does it show a much higher number? If you reduce the number of fields (and hence the amount of data to exchange per item), do more rows appear?

    I doubt it could help, but maybe try the maxReceivedMessageSize attribute in the connection config under C:\Users\You\AppData\Local\Sitecore\Sitecore.Rocks\Connections, although this may do nothing and/or there may be a corresponding setting on the server as well.

    From my scan of what might be the wrong code, I don't see anything that sets a default around 1000 query results on the client or the server.

    - John West
    February 27, 2013 at 11:14 AM

  • Thanks for this.

    Count() timed out as well, fields already reduced to bare minimum (@@name).

    The values in the Connection.xml you pointed me at looked like they would help so I have increased/varied many of these but to no avail. It seems to time out after 1 minute in any case.

    I'll raise a thread in the Rocks forum and see if Jakob is able to help.

    - Chris Lewis
    February 28, 2013 at 1:27 AM

*
*
*
More Posts

Learn More with Sitecore

Newsletter

Enter your email address to see subscription options or manage your existing account

N. Vancouver School District | Read Case Study >

To us, Sitecore has been more than just a CMS, but a development platform. We are thrilled with the depth and sophistication of the Sitecore environment.

- Brian Fairholm, North Vancouver School District