Revisited: Sitecore path in SQL Query

A little over 2 years ago, when we all thought the pandemic might just be a 2-3 month ordeal, I wrote the first article of this blog about how to get Sitecore item paths when directly querying the (master) database. A lot has happened since then, let’s talk about it.

With the release of Sitecore 10.1 all the standard, Out-Of-The-Box items that come with Sitecore are no longer stored in the master/web/core databases. In stead they are delivered as Item Resource Files. These articles go more in depth about the how and why. For this article we mostly care that this breaks the query of my original solution.

The query uses a recursive common table expression where the base case is defined as the item that doesn’t have a ParentID. This is the /sitecore item, but since that item is now no longer in the database, the query can’t find it and doesn’t return any results. To have a working query again this base-case needs updating to contain the item paths and IDs that other items refer to in their ParentID column:

The from (select ''n)t cross apply(... part might surprise some people, but I find this the easiest way to define a hardcoded set of results that is easily extended even by people that aren’t super familiar with SQL (rather than a bunch of union all select’s or having a separate declare @table table (...)).

By default this query is now able to get paths of items in the content, Forms and Media Library folders as well as the most common subfolders of templates. If you want to find items under, for instance, /sitecore/layout/Renderings/Feature, you’ll have to add a line with that path and its ID to the list:

		('{DA61AD50-8FDB-4252-A68F-B4470B1C9FE8}', '/sitecore/layout/Renderings/Feature')

To find these paths and ID’s you can just look at any working Sitecore instance, the IDs will be the same across installs.