Microsoft

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

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!

Thoughts on “SQL Querying Sitecore Database Directly: Get FullPath of Item”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Jamie Stump

My name is Jamie Stump, and I am a Senior Sitecore Consultant at Perficient. I was honored to be named one of only 42 2013 Sitecore MVP’s worldwide. I specialize in Sitecore Architecture and Development and my broad Sitecore experience includes Sitecore installation, configuration and CEP development, including custom DMS implementations for clients. I have implemented Sitecore solutions for a number of industry verticals including manufacturing, healthcare, financial services, advertising and retail. In addition to architecting and implementing Sitecore sites and eCommerce solutions, I also work with other Microsoft Technologies, including the .NET platform and SQL Server. You can read through my older Sitecore related blog posts here and my newer ones here. I graduated with a Bachelor of Science in Information Systems Development from York College of PA. I am originally from the suburbs of Philadelphia, PA, and still reside there with my wife, son, English bulldog and 2 cats.

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram