dbTalk Databases Forums  

newbie - parse filepath field into xml doc

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.xml newbie - parse filepath field into xml doc

microsoft.public.sqlserver.xml microsoft.public.sqlserver.xml


Discuss newbie - parse filepath field into xml doc in the microsoft.public.sqlserver.xml forum.



Reply
Page 1 of 3 1 2 3 >
 
Thread Tools Display Modes
  #1  
Old   
Mike Stokan
 
Posts: n/a

Default newbie - parse filepath field into xml doc - 12-04-2008 , 02:43 PM





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



Reply With Quote
  #2  
Old   
Bob
 
Posts: n/a

Default RE: newbie - parse filepath field into xml doc - 12-05-2008 , 02:34 AM





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:

Quote:
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

Reply With Quote
  #3  
Old   
Bob
 
Posts: n/a

Default RE: newbie - parse filepath field into xml doc - 12-05-2008 , 02:34 AM


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:

Quote:
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

Reply With Quote
  #4  
Old   
Bob
 
Posts: n/a

Default RE: newbie - parse filepath field into xml doc - 12-05-2008 , 02:34 AM


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:

Quote:
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

Reply With Quote
  #5  
Old   
Bob
 
Posts: n/a

Default RE: newbie - parse filepath field into xml doc - 12-05-2008 , 02:34 AM


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:

Quote:
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

Reply With Quote
  #6  
Old   
Bob
 
Posts: n/a

Default RE: newbie - parse filepath field into xml doc - 12-05-2008 , 02:34 AM


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:

Quote:
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

Reply With Quote
  #7  
Old   
Bob
 
Posts: n/a

Default RE: newbie - parse filepath field into xml doc - 12-05-2008 , 02:34 AM


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:

Quote:
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

Reply With Quote
  #8  
Old   
Bob
 
Posts: n/a

Default RE: newbie - parse filepath field into xml doc - 12-05-2008 , 02:34 AM


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:

Quote:
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

Reply With Quote
  #9  
Old   
Bob
 
Posts: n/a

Default RE: newbie - parse filepath field into xml doc - 12-05-2008 , 02:34 AM


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:

Quote:
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

Reply With Quote
  #10  
Old   
Bob
 
Posts: n/a

Default RE: newbie - parse filepath field into xml doc - 12-05-2008 , 02:34 AM


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:

Quote:
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

Reply With Quote
Reply
Page 1 of 3 1 2 3 >

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top