I'm having some trouble getting all of the elements into on long string. Maybe it will help if I post the code that I am trying to optimize. Your solution may work it may just be me having an issue wrapping my head around it. Thanks again!!!

DECLARE @table TABLE

(

rownum INT IDENTITY(1, 1)

PRIMARY KEY

NOT NULL,

CategoryNo INT,

Headline VARCHAR(100)

)

DECLARE @categories VARCHAR(MAX)

DECLARE @tempval1 VARCHAR(50)

DECLARE @tempval2 VARCHAR(1000)

DECLARE @rowcnt INT

DECLARE @maxrows INT

--- Get all the categories for the clip

INSERT @table

SELECT CategoryNo,

Headline

FROM Association a,

Category c

WHERE a.Tag = 'Clip' + @ClipId

AND a.ParentType = 'C'

AND a.ParentNo = c.CategoryNo

SELECT @maxrows = COUNT(*)

FROM @table

SELECT @rowcnt = 1

--- Set category xml start tag

SELECT @categories = '<CategoryList>'

--- Construct category XML for the clip

WHILE @rowcnt <= @maxrows

BEGIN

SELECT @tempval1 = Headline,

@tempval2 = CategoryNo

FROM @table

WHERE rownum = @rowcnt

SET @categories = @categories + '<category><id>' + @tempval2

+ '</id><name><![CDATA[' + @tempval1 + ']]></name></category>'

SELECT @rowcnt = @rowcnt + 1

END

--- Set category xml closing tag

SET @categories = @categories + '</CategoryList>'