Build a file location on the fly

By Tim Chapman , TechRepublic on April 24, 2008

Summary

SQL Server 2005's recursion and XML features help you to easily build file locations.

Events

IBM Technology Conference & Expo 2012
May 22, 2012

One World Hotel, First Avenue, Bandar Utama City Centre, 47800 Petaling Jaya, Selangor

Echelon 2012
June 11 and 12, 2012

University Cultural Centre, National University of Singapore

Startup Asia Jakarta 2012
June 7 and 8, 2012

12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia

MMA Forum Singapore
April 23-25, 2012

Grand Hyatt Singapore

Submit

If you ever work with directory structures on the filing system, you know how challenging it can be to traverse through folders to find specific file(s).

If you store this type of information structure in the database, you are even more aware of what it takes to retrieve the data.

Writing queries to pull this information is sometimes difficult to achieve and inefficient.

You can use the recursion and XML features in SQL Server 2005 to build a file location on the fly.

Example
This example searches for a document and builds the path to the document based upon a parent-folder to child-folder relationship in the database. One file will belong to one folder, which may be a child folder in a long lineage of parent folders.

The ultimate goal is to provide a file to be searched for, and the process will build the location to the file.

I've seen file path locations stored several ways in the database, usually with the purpose of storing the location of a file to be pulled for a Web site.

Most of the time, the full path to a file is stored in one database field, but I have also seen the location of a file "normalized" so that the past must be built when needed.

My goal for this article is to solve the issue of building the path from the hierarchical structure.

The script below creates a Documents table and a Folders table. The Documents table stores the filenames and the folder that the document resides in. The Folders table stores the directory structure of one or more local or network drives.

Most of the work in this example will involve traversing through this folder structure to build the path to the file.

IF OBJECT_ID('Documents','U') IS NOT NULL
 DROP TABLE Documents     

 IF OBJECT_ID('Folders','U') IS NOT NULL
 DROP TABLE Folders     

 IF OBJECT_ID('udf_BuildDocumentPath','FN') IS NOT NULL
 DROP FUNCTION udf_BuildDocumentPath     

 CREATE TABLE Documents
 (
         DocumentID SMALLINT,
         FolderID SMALLINT,
         DocumentName VARCHAR(255)
 )     

 CREATE TABLE Folders
 (
         FolderID SMALLINT,
         ParentFolderID SMALLINT,
         FolderName VARCHAR(255)
 )

The code below adds data to our newly created tables. I am adding data for three documents, all of which are located in the same folder.

INSERT INTO Documents(DocumentID, FolderID, DocumentName)
 VALUES(1,5,'SalesForecast2008.xls')
 INSERT INTO Documents(DocumentID, FolderID, DocumentName)
 VALUES(2,5,'SalesProjection.doc')
 INSERT INTO Documents(DocumentID, FolderID, DocumentName)
 VALUES(3,5,'SalesForecastPresentation.ppt')     

 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)
 VALUES(1,null, 'D:')
 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)
 VALUES(2,1, 'Sales')
 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)
 VALUES(3,2, 'Forecasts')
 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)
 VALUES(4,3, 'Data')
 INSERT INTO Folders(FolderID, ParentFolderID, FolderName)
 VALUES(5,4, '2008')
 GO

The script below creates the function that will build the full path to the file based upon the DocumentID in the Documents table. This function uses a recursive common table expression (CTE) to traverse through the directory structure, linking the child folder ID to the parent folder ID in the table.

Once the set of records are found that comprise the full path to the document, the FOR XML PATH(‘') construct is used to "pivot" these values from values in different rows to values concatenated in the same row. From there, it is just a matter of returning the build path to the caller.

FOR XML Path() is one of my favorite features in SQL Server 2005 because it makes it so easy to take a list of column values from different rows and concatenate them together so that they are on the same row.

It is an ideal tool for dynamically building SQL statements that require list of different values for use in an IN() statement.

CREATE FUNCTION udf_BuildDocumentPath
 (
         @DocumentID SMALLINT
 )
 RETURNS VARCHAR(400)
 AS
 BEGIN
         DECLARE @ReturnPath VARCHAR(400)     

 ;WITH DirectoryPathCTE(DocumentID, FolderID, ParentFolderID, 
 DocumentName, FolderName, LevelNumber)
         AS
         (
         SELECT
                DocumentID, f.FolderID, ParentFolderID, 
                DocumentName, f.FolderName, 0
         FROM
                Documents d
                INNER JOIN folders f on d.FolderID = f.FolderID
         WHERE
                DocumentID = @DocumentID
         UNION ALL
         SELECT
                DocumentID, f.FolderID, f.ParentFolderID, 
                DocumentName, f.FolderName, p.LevelNumber + 1
         FROM
                Folders f
                INNER JOIN DirectoryPathCTE p on p.ParentFolderID 
                = f.FolderID
         )
         SELECT @ReturnPath =
         (
                SELECT
                   FolderName + '' + CASE WHEN LevelNumber = 
                   0 THEN DocumentName ELSE '' END
                FROM
                        DirectoryPathCTE p
                ORDER BY LevelNumber DESC
                FOR XML PATH('')
         )      

         RETURN(@ReturnPath)     

 END
 GO

Now that my function is built, I can call it for every document I have in my Documents table, and the path to the file will be built based upon the DocumentID in the Documents table.

SELECT dbo.udf_BuildDocumentPath(d.DocumentID)
 FROM Documents d

Conclusion
Even if you never need to implement the example in this article, I hope you'll take away some ideas from the recursion and the FOR XML PATH clause to solve some tricky problems you may encounter in the future.

Tim Chapman, an SQL Server database administrator and consultant, works for a bank. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Talkback

Add your opinion

In order to post a comment, you need to be registered. (Sign In or register below)

E-mail* (Will not be displayed)

Password*

Username*

Retype your password*

Comment

Post your comment

ZDNet Asia Live

Pakistan lifts block on Twitter - ZDNet Asia: Pakistan lifts block on TwitterZDNet Asia59 Jakarta 10350, Indones... http://t.co/61n85ajh

15 minutes ago by semarang_photo on twitter

Pakistan lifts block on Twitter http://t.co/WHqoJOqm http://t.co/erFX4aVv #arcavir

15 minutes ago by V_RaV on twitter

http://t.co/VNaZtseV Pakistan lifts block on Twitter: Country restores access after briefly ... http://t.co/5gqegFWK http://t.co/wiqY9ktt

15 minutes ago by RavtachSolution on twitter

Pakistan lifts block on Twitter. http://t.co/y0arswpE

15 minutes ago by zdnetasia on twitter

Mac users' indifference toward security 'worrying'. http://t.co/i7gZ8WVn

30 minutes ago by zdnetasia on twitter

Mac users' indifference toward security 'worrying' - ZDNet Asia: Mac users' indifference toward security 'worryi... http://t.co/CD9pvW08

30 minutes ago by win7antivirus on twitter

RT @zdnetasia: Mac users' indifference toward security 'worrying'. http://t.co/i7gZ8WVn

30 minutes ago by t_phuck on twitter

Mac users' indifference toward security 'worrying' - ZDNet Asia: USA TODAYMac users' indifference toward securit... http://t.co/4EUVidiO

30 minutes ago by Namosofts on twitter

Mac consumers indifferent about security, security vendors warn such mindset is "worrying" http://t.co/ZGIxdg67 #In

30 minutes ago by EllyZDNetAsia on twitter

Mac users take note! RT @zdnetasia: Mac users' indifference toward security 'worrying'. http://t.co/YrLB9btb #mac #apple

30 minutes ago by jolintan on twitter

RT @zdnetasia: Mac users' indifference toward security 'worrying'. http://t.co/i7gZ8WVn

30 minutes ago by KevinZDNetAsia on twitter

Mac users' indifference toward security 'worrying': However, Mac users ZDNet Asia spoke to expressed indifferenc... http://t.co/15DulmWS

30 minutes ago by ArkinOttman54 on twitter

RT @jolintan: Mac users take note! RT @zdnetasia: Mac users' indifference toward security 'worrying'. http://t.co/2RQkfCKt #mac #apple

30 minutes ago by ewenboey on twitter

APAC tech merger and acquisition in Q1 down but value up: http://t.co/V7UkMABl

30 minutes ago by CopenhagenINK on twitter

Mac users' indifference toward security 'worrying' - ZDNet Asia: Mac users' indifference toward security 'worryi... http://t.co/PINqvJxT

45 minutes ago by antivirusdown on twitter

I reckon your view: "CRM is strategy, not software", if a company replicating the approach uses in ERP implementation into CRM, what they...

2 hours ago by wykoong on Gartner: Mobile CRM gives better ROI than social

This video will teach you about the Excel fill handle but also provide you with a workook to download... http://www.youtube.com/watch?v=...

19 hours ago by TradeBrother on A quick fill handle trick for Microsoft Excel

waiting...

2 days ago by eapete on What should count in a company's market value?

Boy, you've opened a can of worms now.

Wait for the rants & raves.

2 days ago by eapete on What should count in a company's market value?

I was puzzling before this whether to replicate the success formula we executed for a financial institute, and come out with a standard s...

3 days ago by wykoong on Drop the egos, copy ideas, then innovate

ZDNet / Techguide / Database

Build a file location on the fly

Summary

Events

IBM Technology Conference & Expo 2012 May 22, 2012 One World Hotel, First Avenue, Bandar Utama City Centre, 47800 Petaling Jaya, Selangor

Echelon 2012 June 11 and 12, 2012University Cultural Centre, National University of Singapore

Startup Asia Jakarta 2012 June 7 and 8, 201212th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia

MMA Forum Singapore April 23-25, 2012Grand Hyatt Singapore

Email

Talkback

Resource Centre Useful content from our premier sponsors

Database TechGuides

ZDNet Asia Live

Keep up with ZDNet Asia

Whitepapers

Downloads

Database News

Inside ZDNet Asia

Sponsored

Services

IBM Technology Conference & Expo 2012
May 22, 2012

One World Hotel, First Avenue, Bandar Utama City Centre, 47800 Petaling Jaya, Selangor

Echelon 2012
June 11 and 12, 2012

University Cultural Centre, National University of Singapore

Startup Asia Jakarta 2012
June 7 and 8, 2012

12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia

MMA Forum Singapore
April 23-25, 2012

Grand Hyatt Singapore