Comments on cloud.net: SharePoint List in SQL

Thanks Ray. I incorporated your suggestions and I...

2010-02-03T09:59:59.678+11:00

Thanks Ray.

I incorporated your suggestions and I think this works now:

ALTER procedure [dbo].[usp_PrintList] (@ListId uniqueidentifier)
AS
BEGIN

DECLARE @XMLFields TABLE (Row INT IDENTITY, Field XML);
DECLARE @xFields XML;
SELECT @xFields = (SELECT tp_Fields as XML
FROM Lists
WHERE (tp_ID = @ListId))

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

DECLARE @sql VARCHAR(MAX), @field XML,
@colname VARCHAR(30), @Type VARCHAR(30), @dispname VARCHAR(255);

SET @sql = 'SELECT t1.tp_ID as [ID], '

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 Top 1 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 )
SET @sql = @sql + ' FROM UserData t1 WHERE t1.tp_ListId = '''+
CAST(@ListId AS VARCHAR(50)) +''' AND t1.tp_RowOrdinal = 0 ORDER BY t1.tp_ID asc'

PRINT @sql
EXEC(@sql)

END

David. 1.- From the err msg, the xml string appea...

2010-02-03T09:41:35.659+11:00

David.

1.- From the err msg, the xml string appears to be truncated/incomplete.
I can you modify the proc to print out the variable values, to see where and when it breaks?

2.- Without knowing your list schema it's hard to be sure; but it looks like some of your lookups may have more than one value; which I didn't cater for.
You could do a "Select top 1" in the subqueries or try and concatenate the return rows in to a comma delimited varchar variable and with an "IN()".

I aslo get this error on soem lists: (46 row(s) af...

2010-02-03T07:05:08.294+11:00

I aslo get this error on soem lists: (46 row(s) affected)
SELECT t1.nvarchar1 as [Summary], t1.nvarchar3 as [Group], t1.ntext2 as [Description], t1.ntext3 as [Business Case], (SELECT tp_Title FROM
UserInfo WHERE tp_ID = t1.int1) as [Requestor], (SELECT nvarchar1 FROM UserData WHERE tp_ListId = '{d3576909-5291-4bf1-ae4c-b991c86d5245}' AND tp_ID = t1.int2) as [Business Owner], t1.nvarchar4 as [Priority], (SELECT tp_Title FROM
UserInfo WHERE tp_ID = t1.int3) as [Assigned To], t1.nvarchar5 as [Request Type], (SELECT nvarchar1 FROM UserData WHERE tp_ListId = '{def12deb-02cf-4178-bf53-1bd691e5fe97}' AND tp_ID = t1.int4) as [Category or System], t1.nvarchar6 as [Status], t1.ntext4 as [Status Text], t1.datetime1 as [Status Text Date], t1.nvarchar7 as [Effort], t1.float1 as [%Complete], t1.datetime2 as [Target Date], t1.datetime4 as [Start Date], t1.datetime3 as [Completed Date], t1.float2 as [Parent Request ID], t1.float3 as [Parent Task ID], t1.float4 as [Task ID], t1.nvarchar8 as [Copy IM Request Task ID], t1.nvarchar9 as [IM Request Tasks - Status Text Copy and Date Stamp], t1.nvarchar10 as [Copy IM Request Task ID and copy request date], t1.nvarchar11 as [IM Task Closed - Notify for Request Closure], t1.nvarchar12 as [Assigned To Person Notification], t1.bit1 as [Assigned To Person?], t1.nvarchar13 as [Assigned To Person Notification (2)], t1.nvarchar14 as [Email Notification To All Groups], t1.bit2 as [Approved Close?], t1.nvarchar15 as [Assigned To Person Notification (3)], t1.nvarchar16 as [Email Notification To All Groups (2)], t1.tp_ContentType as [Content Type], t1.datetime5 as [Request Date], t1.nvarchar17 as [Task Closure Data Cleanup], t1.nvarchar18 as [Task Closure Data Cleanup (2)], t1.nvarchar20 as [IM Task Closed - Notify for Request Closure], t1.sql_variant1 as [Date Closed Plus 8], t1.nvarchar19 as [Task Closure Data Reminder], t1.nvarchar21 as [Task Closure Data Reminder (3)], t1.ntext5 as [Link to Parent Request], t1.sql_variant2 as [Date Closed Plus 30], t1.nvarchar22 as [Close Auto Ent Rpoeting Log Items], t1.sql_variant3 as [Task Type] FROM UserData t1 WHERE t1.tp_ListId = '9A1C68ED-0EB1-4A93-813E-6531F3C0FE21' AND t1.tp_RowOrdinal = 0
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This is great! My only issue is it still does not ...

2010-02-03T02:18:16.208+11:00

This is great! My only issue is it still does not work for lists with a large number of columns. I changed @sql to MAX, but I still get:
XML parsing: line 1, character 5741, end tag does not match start tag

This is much appreciated and could solve a lot of my issues. - Thanks

MC Burley: Glad you liked it and commented; your r...

2009-12-21T09:54:51.424+11:00

MC Burley: Glad you liked it and commented; your right... the 8000 char limit was my miscalculation based on SQL2000. With SQL +2005 when data in a MAX data type exceeds 8 KB, an over-flow page is used. So your list metadata can be up to 2GB, in effect no limit. I've removed the limitation from the article.
The query (@sql) length is still 8000 chars, so if you have too many columns change this to a MAX.

Merry Christmas.

You made my day with this one. Not sure I understa...

2009-12-19T06:29:18.281+11:00

You made my day with this one. Not sure I understand the 8000 char limitation? I have a form library for InfoPath with 70 float columns promoted so far. Used your code to get the rowordinal in order to match the column names. I have about 50 more columns to add. Question: Running len(cast((tp_Fields ) as varchar(max)) returns 23460. How big can tp_Fields get before this breaks?
Thanks for a great post.