2009-11-03

SharePoint List in SQL


I was recently tasked with Exporting a SharePoint list to Excel for users logging in using Forms Authentication.
I scratched my head for a minute and I came up with creating a Reporting Services report that queries the SP DB directly.

Issues: I wanted the query to be dynamic and use the exact same SP columns. So if a user adds a field or changes a column name I don't need to amend the SSRS report.

Solution: I created a Stored Proc, that takes 1 parameter (ListID). The proc gets the fields for the list then builds a query and executes it. The Stored Proc needs to reside on the same Content DB.

Code:


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 )
  SET @sql = @sql + ' FROM UserData t1 WHERE t1.tp_ListId = '''+ 
      CAST(@ListId AS VARCHAR(50)) +''' AND t1.tp_RowOrdinal = 0'
 
  PRINT @sql
  EXEC(@sql)
END

Limitations:

  1. It doesn't get the Display Name for field references (ie not customized field)

  2. It only looks at the Title of the lookup list/user, and then only on custom look up.

Example:


MS SQL Management Studio View




SharePoint View




Keywords: Export to spreadsheet, SQL field view, AllUserData

File link




6 comments :

  1. 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.

    ReplyDelete
  2. 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.

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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.

    ReplyDelete
  5. 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()".

    ReplyDelete
  6. 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

    ReplyDelete