Tag: url

; WITH PathXml AS (

/* -- This gives nodes with their 'urlName' property
 -- not in recycle bin,
 -- level > 1 which excludes the top level documents which are not included in the url */
 SELECT
 nodeId,
 cast([xml] as xml).query('data(//@urlName[1])').value('.', 'varchar(max)') AS Path
 FROM cmsContentXml x
 JOIN umbracoNode n ON x.nodeId = n.id AND n.trashed = 0 AND n.level > 1
)

SELECT
 u.id,
 u.path,
 '/' +
 /* This is to get the top level document */
 IsNull((SELECT
 pl.Path + '/'
 FROM PathXml pl
 WHERE ',' + u.path + ',' LIKE '%,' + CAST(pl.nodeId AS VARCHAR(MAX)) + ',%'
 ORDER BY CHARINDEX(',' + CAST(pl.nodeId AS VARCHAR(MAX)) + ',',
 ',' + u.path + ',')

FOR XML PATH('')),
 '') AS Url,
 u.text PageName
FROM umbracoNode u
WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' /* Document node */
AND trashed = 0
ORDER BY 3 /* Url */