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('').value('e[1]','VARCHAR(MAX)')
{ count(/doc/Tags) }
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:
Post a Comment