Tuesday, March 3, 2009

SQL Server- Get Rows AS XML, Traverse XML in TSQL

I have a table 'Tags' , see below


To get the rows as XMl

SELECT * FROM Tags for xml auto

The result will be

I want to get the tags separated by coma for the given music id, i wrote a function that
will traverse the XML and return the tags as a single row separated by coma

CREATE FUNCTION [dbo].GetMusicTags
(
@MusicID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Xdoc XML
DECLARE @Count INT
DECLARE @i INT
DECLARE @Tag VARCHAR(MAX)
DECLARE @Temp VARCHAR(MAX)
--
SET @i=1
SET @Tag=''
SET @Temp=''
--
SET @Xdoc=''+(SELECT * FROM Tags WHERE MusicID=@MusicID for xml auto)+''
SET @Count = @Xdoc.query('
{ count(/doc/Tags) }
').value('e[1]','VARCHAR(MAX)')

WHILE @i <= @Count
BEGIN
SELECT @Temp= e.x.value('@Tag[1]', 'VARCHAR(MAX)') FROM
@Xdoc.nodes('/doc/Tags[position()=sql:variable("@i")]') e(x)
SET @Tag=@Tag+@Temp
IF @i<>@Count
SET @Tag=@Tag+','
SET @i = @i + 1
END
RETURN @Tag
END
GO

Result will be


Download Sql Script

1 comment:

Priyan R said...
This comment has been removed by the author.