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

Just a quick note about the script, the

-- Parse them out into separate components
-- drive, folder1, folder2, full filename, filename only, extension

comment at the bottom was actually a TODO, not what the script really does ...

"Bob" wrote:

> This is a rough example but hopefully you get the idea:
>
> USE master
> GO
>
> -- Modified from
> http://www.microsoft.com/communities...xp=&sloc=en-us
> 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>' + REPLACE( @str, @separator,'</tag><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
> >
> >
> >