newbie - parse filepath field into xml doc

This is a rough example but hopefully you get the idea:

USE master
GO

-- Modified from
http://www.microsoft.com/communities/newsgroups/list/en-us/default.asp...uery=sp
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.Split') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.Split
GO

CREATE FUNCTION dbo.Split
(
@str VARCHAR(MAX),
@separator VARCHAR(MAX)
)
RETURNS @tableOutput TABLE ( row_id INT IDENTITY PRIMARY KEY, result
VARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML;

SET @xml = CAST( ' <tag>' ) +
'</tag>' AS XML )

INSERT @tableOutput ( result )
SELECT T.i.value('.', 'VARCHAR(255)') result
FROM @xml.nodes('tag') T(i);

RETURN
END
GO

USE tempdb
GO

DROP TABLE #tmp
CREATE TABLE #tmp( file_id INT IDENTITY UNIQUE, filepath VARCHAR(255)
PRIMARY KEY )
GO

INSERT INTO #tmp VALUES ( 'c:\temp\' )
INSERT INTO #tmp VALUES ( 'c:\temp\temp.txt' )
INSERT INTO #tmp VALUES ( 'c:\temp\temp2\temp.csv' )
GO

-- Parse them out into separate components
-- drive, folder1, folder2, full filename, filename only, extension
SELECT file_id, result AS filepath_element
FROM #tmp t
CROSS APPLY master.dbo.Split( t.filepath, '\' )
FOR XML RAW
GO

HTH
wBob

"Mike Stokan" wrote:

> I have a SQL 2005 table that stores filenames with attached path. Could
> someone point me to a way to parse the path/filename into an XML hierarchy?
>
> Many thanks.
>
> mike
>
>
>