How do I get an item path from the Sitecore Master DB?

Question

I have a direct SQL query to pull some specific item from the DB. Can get the ItemID and Field name I am interested in... but I want to give my editors the path to the item instead of making them search for the item ID. How can I get this path with a SQL query? For the record, here is my current query:

SELECT ItemId, Language, Value, I.Name FROM Fields F 
JOIN Items I ON F.FieldId = I.ID
WHERE Value LIKE '%style="color: #999999%' 
ORDER BY Language, ItemID

I had some translators incorrectly copy-and-paste HTML inside Content Editor and these superfluous style tags came along for the ride in many places. I would like to get a full list of Items (with content tree path) that are affected. DB Schema is Sitecore 6.6.

Answer 1

I would strongly recommend writing an utility that utilizes Sitecore's API for this type of work. Using something like Sitecore PowerShell (https://marketplace.sitecore.net/en/Modules/Sitecore_PowerShell_console.aspx) would also be a good option if you have that installed already.

That being said the below blog post will provide you with an example of creating a function in SQL to easily get ItemPath.

http://blogs.perficient.com/microsoft/2014/05/sql-querying-sitecore-database-directly-get-fullpath-of-item/

Answer 2

Actually, it is very not recommended to do any manipulations with Sitecore data on MS SQL level. It is better to find a way how to resolve this problem via Sitecore API.

However you can get paths of items using recursive SQL query:

with Items1 (nm, id1, pathstr)
as (select Name, id, cast('/sitecore' as nvarchar(MAX)) 
   from Items
   where ParentID = '00000000-0000-0000-0000-000000000000'
union all
   select Items.Name, Items.id, Items1.pathstr +'/'+ Items.Name
   from Items 
     inner join Items1 on Items1.id1 = Items.ParentID) 
select id1 as ItemId, nm as Name, pathstr as [Path], F.Id as FieldId, F.Value as FieldValue
from Items1 I
JOIN Fields F ON F.ItemId = I.id1
WHERE Value LIKE '%style="color: #999999%' 
ORDER BY Language, ItemID

Recursive query is required because paths are not saved as field.

P.S. Query is for Sitecore 7.2, I do not have Sitecore 6.6 on my machine. You can adjust this query for Sitecore 6.6 if there are any differences.

Answer 3

As @Anton mentioned, Sitecore does not support direct querying against the database so this is generally not advisable.

If you are in the Sitecore desktop, you can use the XPath Builder within the Developer Center to perform a query to retrieve these items. In the sample below, change @YourFieldName to the relevant field name.

/sitecore/content//*[contains(@YourFieldName, 'style="color: #999999')]