Query for Document Path - SQL Server

Tirumala Katari

Dear

Can someone please tell me the query to display the path for each document under a folder?

Ex. Enterprise\Folder1\Folder2\Document.txt

Find more posts tagged with

Comments

Brian_Walsh

This is going to depend upon the version that you are running. In older versions you would have to identify the DataID in DTree for the document and then walk up the tree of ParentIDs. Once you establish one chain however it would be the same for all documents within that folder.

In more recent versions you would look at using the DTreeAncestors table. This table has 2 columns, DataID and AncestorID. Each DataID will have a number of entries representing all ParentIDs above it.

Brian G Walsh
Principal Technical Analyst

Content Server Team

OpenText Corporation
1-800-540-7292
bwalsh@opentext.com

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, January 26, 2016 5:56 AM
To: eLink Recipient
Subject: Query for Document Path - SQL Server

Query for Document Path - SQL Server

Posted by Katari, Tirumala On 01/26/2016 05:55 AM

Dear

Can someone please tell me the query to display the path for each document under a folder?

Ex. Enterprise\Folder1\Folder2\Document.txt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Al Picard

You may want to tinker with this query in a LiveReport where %1 is your document parameter:

select rtrim(reverse(sys_connect_by_path(reverse(name || '(' || subtype || ')' ),'/')),'/') "Path Root (in reverse order)" from dtree where connect_by_isleaf = 1 start with dataid=%1 connect by prior abs(parentid)=dataid

Al Picard

Colin_Schmidt

I thought "sys_connect_by_path" was Oracle-only.

Colin J.

On Tue, Jan 26, 2016 at 9:52 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE Query for Document Path - SQL Server Posted byPicard, AlOn 01/26/2016 09:45 AM

You may want to tinker with this query in a LiveReport where %1 is your document parameter:select rtrim(reverse(sys_connect_by_path(reverse(name || '(' || subtype || ')' ),'/')),'/') "Path Root (in reverse order)" from dtree where connect_by_isleaf = 1 start with dataid=%1 connect by prior abs(parentid)=dataidAl Picard
[To post a comment, use the normal reply function]
Topic: Query for Document Path - SQL Server
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from WestRockCompany (www.westrock.com) or itssubsidiaries, which may be confidential, privileged or otherwise protected fromdisclosure. The information is intended to be disclosed to and used by only thenamed recipient(s). If you are not the intendedrecipient, then your review, use, disclosure, printing, copying, ordistribution of this message or its contents is prohibited. If you havereceived this message in error, please notify WestRock immediately at postmaster@westrock.com, and delete themessage from your system. Unless previously authorized in writing, thismessage does not constitute an offer, acceptance, or agreement of any kind.Sender is not liable for damage, errors or omissions related to or caused bytransmission of this message. © WestRock Company.

William_Collinsworth

We use the following table valued function in Microsoft SQL:

CREATE FUNCTION [GetFullPaths] (

@DataID BIGINT

)

RETURNS

@FullPaths TABLE (

DataID BIGINT PRIMARY KEY,

ParentID BIGINT NOT NULL,

FullPath NVARCHAR(MAX) NOT NULL

)

BEGIN

INSERT INTO

@FullPaths

SELECT

DT.DataID,

DT.ParentID,

FullPath = DT.Name

FROM

DTree DT

WHERE

DT.DataID = @DataID

;

WHILE EXISTS(

SELECT

FROM

@FullPaths FP

WHERE

NOT FP.ParentID = -1

)

BEGIN

UPDATE

SET

ParentID = DT.ParentID,

FullPath = DT.Name + ':' + FP.FullPath

FROM

@FullPaths FP

JOIN DTree DT

ON DT.DataID = FP.ParentID

;

END;

RETURN;

END;

You would call the function like so:

SELECT

FP.FullPath

FROM

DTree DT

CROSS APPLY GetFullPaths(DT.DataID) FP

WHERE

DT.ParentID = 2000

;

Tirumala Katari

Thanks to all of you.

Agarwal_Gourav

Hi ,

Your Solution was of great use but we being on production are not allowed to run a update query.

Can we have a solution without DB modification.

Thanks

Lindsay_Davies

This is what I use to report a folder hierarchy in SQL server.
It is from a Livereport (autolivereport format). Parameter 1 (user input #1) is number.

It is an exampe of a recursive common table expression (CTE), beginning with WITH SubTree (columns) AS (sql...)
and the required select from that CTE.

The comment out WHERE clause would restrict the output to report only the folder objects.
The output is ordered by pathstr so that the information appears in proper hierarchical order.
Folders names are 'indented' using replicate so that you can see find your way round more easily.

Hope you find it useful.

Regards
Lindsay
Renewable Energy Systems

/* SQL BEGINS */

WITH SubTree ( Name, DataID, ParentID, level, subtype, pathstr)
AS
(Select Name, DataID, ParentID, 0, subtype, CAST (name as NVARCHAR(MAX))
FROM DTree WHERE DataID = %1
UNION ALL
SELECT p.Name, p.DataID, p.ParentID, level + 1, p.subtype,
pathstr + ':' + cast(p.Name AS nvarchar(max))
FROM DTree p
INNER JOIN SubTree s ON s.DataID = p.ParentID
/* WHERE Subtype = 0 */
)

Select REPLICATE('..\',level) + Name as Name, DataID, level, subtype, pathstr as "Full Path"
FROM SubTree ORDER BY pathstr, DataID

/* SQL ENDS */

SAMPLE OUTPUT

Name	DataID	level	subtype	Full Path
Folder1	40523876	0	0	Folder1
..\Folder 2	40523813	1	0	Folder1:Folder 2
..\..\ECM1.txt	40523971	2	144	Folder1:Folder 2:ECM1.txt
..\Folder 3	40524167	1	0	Folder1:Folder 3
..\..\Folder 3.1	40524567	2	0	Folder1:Folder 3:Folder 3.1
..\..\..\ECM2.txt	40523877	3	144	Folder1:Folder 3:Folder 3.1:ECM2.txt
..\Folder 4	40522874	1	0	Folder1:Folder 4

Prem_K

hello Lindsay,

Thanks for the query.This query helped me to get the required output by small minor modifications.

cheers

Narendar_Reddyk

I want to find out the total number of items in a particuler location but

1. The location has 3 sub folders, ex: A, B and C
2. I want to exclude the total number of items from C, and need only the total number of items from A and B only

I can use Dtreeancestors but how to exclude folder C ?? (We are using SQL Server and it is 9.7.1)

Can any help on this would be greatly appreciated

Nizar Ghazal

Quoted Narendar Reddyk on 05/14/2018 08:44 AM:

I want to find out the total number of items in a particuler location but

1. The location has 3 sub folders, ex: A, B and C
2. I want to exclude the total number of items from C, and need only the total number of items from A and B only

I can use Dtreeancestors but how to exclude folder C ?? (We are using SQL Server and it is 9.7.1)

Can any help on this would be greatly appreciated

Just make sure you have a WHERE clause where AncestorID <> the nodeID of C.

That will exclude everything under folder C, which means even other folders, objects, etc.

Colin_Schmidt

If this is a specific Folder, then you’ll know which on to exclude. If “C” is a common name, one found in other directories, you can exclude it by the name. Whatever way you go, you have to know which one you want to exclude. Knowing that, the Folder will have to be hard-coded.

Colin J

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, May 14, 2018 7:28 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Query for Document Path - SQL Server 2

Query for Document Path - SQL Server 2

Posted bynarendar.reddyk@philips.com (Reddyk, Narendar) On 05/14/2018 08:24 AM

I want to find out the total number of items in a particuler location but

1. The location has 3 sub folders, ex: A, B and C
2. I want to exclude the total number of items from C, and need only the total number of items from A and B only

I can use Dtreeancestors but how to exclude folder C ?? (We are using SQL Server and it is 9.7.1)

Can any help on this would be greatly appreciated

[To post a comment, use the normal reply function]
Topic:	Query for Document Path - SQL Server
Forum:	Content Server LiveReports Forum
Content Server:	My Support

This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.