Get the Node URl from the Umbraco (4.7) Database

Question

I have a batch that loads some information from the umbraco (4.7) database.

I need to extract the Link to document node property...

The Api in the site does it like umbraco.library.NiceUrl(nodeid)... But I have one console application that does not have acces to the umbraco stuff...

1) Where can I find this "nice url" of a node in the database?
2) If not (or too complex), how it is possible to configure umbraco API (4.7) library with a console application?

Zeph · Answer 1 · 2016-09-16T13:13:51.603

I tried the above answers but they require mysql (need sql server), a clr function, or only include info about the url info for the current document. For children and documents deep in the navigation path, I wanted to include the full path. The below works for me

; WITH PathXml AS (

    /* -- This just 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
    un.id,
    un.path,
    '/' + 
    /* IsNull after the leading '/'. This will handle the top level document */
    IsNull((SELECT 
                pl.Path + '/'  /* Ok to end with a / */
             FROM PathXml pl 

             /* e.g. ',-1,1071,1072,1189,' LIKE '%,1072,%' */
             WHERE ',' + un.path + ',' LIKE '%,' + CAST(pl.nodeId AS VARCHAR(MAX)) + ',%'

             /* order by the position of ',1072,' in ',-1,1071,1072,1189,' */
             ORDER BY CHARINDEX(',' + CAST(pl.nodeId AS VARCHAR(MAX)) + ',',
                                ',' + un.path + ',')

             FOR XML PATH('')), 
    '') AS Url,
    un.text PageName
FROM umbracoNode un
WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' /* "Document" https://our.umbraco.org/apidocs/csharp/api/Umbraco.Core.Constants.ObjectTypes.html#Umbraco_Core_Constants_ObjectTypes_Document */
AND trashed = 0
ORDER BY 3 /* Url */

This works on some occasions, but in my case I am searching nodes, and when the same node is matched multiple times then the URL segments duplicate. i.e. `/global/global/global/` if the global page is found 3 times. `nodeObjectType ` is was a useful titbit though — JDandChips, Dec 01 '16 at 16:07
Nice one, thanks! Adapted to display the URLs when querying UmbracoLog for recent activity — agrath, Jun 21 '18 at 01:39

score 1 · Answer 2 · answered Apr 29 '16 at 08:10

1

    SELECT 
    cast([xml] as xml).query('data(//@urlName[1])').value('.', 'varchar(20)') AS PATH
    FROM cmsContentXml where nodeid = 19802

My 2 cents worth

answered Apr 29 '16 at 08:10

pixelagent

33
4

score 0 · Accepted Answer · answered Aug 19 '15 at 22:48

0

select 
    GROUP_CONCAT(EXTRACTVALUE(xml, "//@urlName") separator '/') as PATH
from 
    cmscontentxml x inner join 
    umbraconode n on FIND_IN_SET(x.NODEID,n.PATH)>0
where n.ID=19802 -- your id
group by n.ID;

answered Aug 19 '15 at 22:48

serge

13,940
35
121
205

Get the Node URl from the Umbraco (4.7) Database

3 Answers3