SQL Querying Sitecore Database Directly: Get FullPath of Item

It’s not often that a Sitecore developer has to write SQL queries against the Sitecore databases because the Sitecore API handles basic CRUD operations for the developer.  It’s also a best practice to use the API so that all the hooks and handlers and pipelines and events fire the way they’re designed to.  However, there are certain scenarios where a developer might find themselves in need of writing a query to get at the data directly.  One of those situations is when creating custom Engagement Analytics Reports within Sitecore.  In these cases, sometimes a developer may need to return information that is easy to retrieve via the API, but harder to get at when pulling directly from the underlying raw data.  Keep reading show how a developer can create a function that will return the Full Path of a given item in their Sitecore database.  The function can be used in stored procedures or ad-hoc queries for reporting purposes.
In any custom Sitecore reports that display information about Items, it is crucial to return the full path of the Item(s) being reported upon so that the viewer of the report can correctly identify the information they’re reading.  This is critical because items throughout the Sitecore Content Tree can share names, and without the path, the information could be misinterpreted.  The following SQL function will take input parameters of an Item ID along with a blank string and return the full path for the item as a string.

USE [Sitecore_Master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetItemPath]
(
    @ParentID UNIQUEIDENTIFIER,
    @CurrentPath VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @NewParentID UNIQUEIDENTIFIER,
        @EmptyGUID UNIQUEIDENTIFIER
    SELECT @EmptyGUID = CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER)
    SELECT @CurrentPath = '/' + i.Name + @CurrentPath,
        @NewParentID = i.ParentID
    FROM Items AS i
    WHERE i.ID = @ParentID
    if(@NewParentID != @EmptyGUID)
    BEGIN
        SELECT @CurrentPath = dbo.GetItemPath(@NewParentID, @CurrentPath)
    END
    RETURN @CurrentPath
END

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Here’s an example of the function in use:

SELECT i.ID, i.Name, dbo.GetItemPath(i.ID, '') AS FullPath
FROM Items AS i

Using this function on any Custom Engagement Analytics Reports that display information about an Item can help avoid confusion as to exactly what Item is being reported on!