Sitecore Rocks Query Analyzer

Hi All,

       Today we try to cover basics of Sitecore Rocks Query Analyzer. It is the top most features of Sitecore Rocks.

Query analyzer have the sheer power.

Let's start by opening the Query Analyzer.  Right click on a database, navigate under the Tools menu and select "Query Analyzer" from the menu.

If you've never worked with the Query Analyzer before, the best way to get familiar with what's available is to start with the help commands.  Simply enter "help" for the query and execute.  This will return the list of commands available at our disposal.

We can dive deeper in the commands help by appending a command to the help query.  For example, if you enter "help select" and execute this query you will see all the options and syntax for the select command.

Now that we have a basic idea what commands are available let's execute some queries and see the results.

Sitecore Select Query Samples:

Simple Select statement

select * from /sitecore/content/Home

Select child items

select * from /sitecore/*

Select descendents

select * from /sitecore//*

Select descendents with filter

select * from /sitecore//*[@Title = "Welcome to Sitecore"]

Select columns

select @Title, @Text from /sitecore/content/Home

Select columns with aliases

select @Title as Caption, @Text as Body from /sitecore/content/Home

Select expression

select @Title + ", " + @Text as Value from /sitecore/content/Home

Select item ID

select @@ID from /sitecore/content/Home

Select item name

select @@Name from /sitecore/content/Home

Select item path

select @@Path from /sitecore/content/Home

Select item template

select @@TemplateID, @@TemplateName, @@TemplateKey from /sitecore/content/Home

Select with space escaping

If a field contains a space, the field name can be escaped using hashes #.

select @#Body Text# from /sitecore/content/Home

Select using Distinct

select distinct @Title from /sitecore/content/*

Select using Order By

Notice that the result set is order by column names, not field names.

select @@Name as Name from /sitecore/content//* order by Name

Select using Order By descendent

Notice that the result set is order by column names, not field names.

select @@Name as Name from /sitecore/content//* order by Name desc

Select using Distinct and Order By

select distinct Name as Name from /sitecore/content//* order by Name

Sitecore Query Delete Samples

Delete an item

delete from /sitecore/content/Home

Delete children

delete from /sitecore/content/*

Delete descendents with a specific template

delete from /sitecore/content//*[@@templatename = 'News']

Delete items created by a specific user

delete from //*[@#__Created By# = 'sitecore\admin' or @#__Updated By# = 'sitecore\admin']

Delete items that were updated recently

delete from /sitecore/content//*[@__Updated > '20111231T235959']

Scary¶

delete from /*

Sitecore Query Update Samples

Update single field

update set @Text = "Welcome to Sitecore" from /sitecore/content/Home

Update multiple fields

update set @Title = "Sitecore", @Text = "Hello" from /sitecore/content/Home

Update multiple fields in multiple items

update set @Title = "Sitecore", @Text = "Hello" from /sitecore/content/*[@@TemplateName = "Sample Item"]

Reset Updated fields to Created fields

update set @#__Updated By# = @#__Created By#, @__Updated = @__Created, from /sitecore/content/Home

Expressions

update set @Text = "Title: " + @Title from /sitecore/content/Home

Complex Query:

update set @testdroplink ="{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}" from /sitecore/content/Home//*[@testdroplink = ""]

This query update the testdroplink field from all the descendant child's under the home node which has blank testdroplink field value.

I hope this blog help you to understand the Sitecore Rocks Query Analyzer.

Thanks

Arun Sharma