--http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx
CREATE procedure [dbo].[usp_PrintList] (@ListId uniqueidentifier)
AS
BEGIN
	DECLARE @XMLFields TABLE (Row INT IDENTITY, Field XML);
	DECLARE @xFields XML; 

	SELECT @xFields = (SELECT cast(replace(cast(tp_Fields as varchar(max)),'<FieldRef','<Field') as XML) as Fields
						FROM Lists 
						WHERE (tp_ID = @ListId))

	INSERT INTO	@XMLFields
		SELECT Tbl.xFlds.query('.') from @xFields.nodes('/Field') as Tbl(xFlds)

	DECLARE @sql VARCHAR(8000), @field XML, @colname VARCHAR(30), @Type VARCHAR(30), @dispname VARCHAR(255);
	
	SET @sql = 'SELECT '

	DECLARE tmpCursor CURSOR FOR
		SELECT Field, 't1.'+ Field.value('(/Field/@ColName)[1]', 'varchar(max)') , 
				Field.value('(/Field/@Type)[1]', 'varchar(max)'), ' as ['+ isnull(Field.value('(/Field/@DisplayName)[1]', 'varchar(max)'), 
				Field.value('(/Field/@Name)[1]', 'varchar(max)')) +'], '
		FROM @XMLFields

	OPEN tmpCursor
	FETCH NEXT FROM tmpCursor INTO @field, @colname, @Type, @dispname
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF(@colname IS NOT NULL)
			BEGIN	
				SET @sql = (CASE @Type
						WHEN 'Lookup' THEN @sql + '(SELECT nvarchar1 FROM UserData WHERE tp_ListId = '''+ (@field.value('(/Field/@List)[1]', 'varchar(max)')) +''' AND tp_ID = '+ @colname +')' + @dispname		
						
						WHEN 'User' THEN @sql + '(SELECT tp_Title FROM UserInfo WHERE tp_ID = '+ @colname +')' + @dispname		
						
						ELSE @sql + @colname + @dispname
						END)
	
			END
			FETCH NEXT FROM tmpCursor INTO @field, @colname, @Type, @dispname
		END
	CLOSE tmpCursor
	DEALLOCATE tmpCursor  
	 
	--strip off last comma
	SET @sql = SUBSTRING( RTRIM(@sql), 1, LEN(@sql) - 1 )  + ' FROM UserData t1 WHERE t1.tp_ListId = '''+ CAST(@ListId AS VARCHAR(50)) +''' AND t1.tp_RowOrdinal = 0'

	PRINT(@sql);
	EXEC(@sql);
END