CODEWITHSUNDEEP
sqlsql-serversharepointsharepoint-2010
Santhosh
Santhosh

Reputation: 209

How to Query Sharepoint List Data?

Does anyone know how to fetch Sharepoint List data using sql query based on the particular sharepoint user.

Upvotes: 8

Views: 48762

Answers (4)

Missy Rad
Missy Rad

Reputation: 1

First, your SP list will need a field that captures the users account info. Using SQL Report Builder with a SharePoint list data connection, set a parameter on your report for @User. Set this as a hidden parameter. Set the default value of this parameter to =Right(User!UserID,8): this will give you the last 8 characters of the Users Account info which you can then filter your dataset on (your account info may be more or less than 8 so build based on your settings). You will need to set a filter on the dataset or table itself to match your field in your list against the parameter.

Upvotes: 0

Andrey Morozov
Andrey Morozov

Reputation: 7979

Solution for my case - display SP list

select l.tp_title
, n1.title.value('(text())[1]', 'nvarchar(32)') as Title
, n2.[status].value('(text())[1]', 'nvarchar(32)') as [Status]
, n3.[priority].value('(text())[1]', 'nvarchar(32)') as [Priority]
from Lists l
join UserData u on u.tp_ListId = l.tp_ID
cross apply tp_ColumnSet.nodes('/nvarchar1') AS n1(title)
cross apply tp_ColumnSet.nodes('/nvarchar3') AS n2([status])
cross apply tp_ColumnSet.nodes('/nvarchar4') AS n3([priority])
where l.tp_title = 'LIST NAME'

Upvotes: 0

Adrian Sullivan
Adrian Sullivan

Reputation: 397

As long as it is SharePoint 2010 it is still relatively easy to pull from SQL as long as you have the list id GUID

See here

    SELECT
    ud.tp_ID
    , ud.tp_ListId
    , ud.tp_Author
    , ud.nvarchar1
    , ud.nvarchar2
    , ud.nvarchar3
    , ud.nvarchar4
    , ud.nvarchar5
    , ud.nvarchar6
    , ud.nvarchar7
    , ud.nvarchar8
    , ud.nvarchar9
    , ud.nvarchar10
    , ud.nvarchar11
    , ud.nvarchar12
    , ud.*
    FROM dbo.UserData ud
    WHERE (ud.tp_ListId =  '[{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}')

Upvotes: 5

Velin Georgiev
Velin Georgiev

Reputation: 2489

You cannot use should not really be using SQL to get data from SharePoint list. Most common way of doing this is custom solution where you can use CAML (SPQuery) to retrieve data for list items (created by the user for example) http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.aspx or else you can expose the SharePoint services to get the same results http://msdn.microsoft.com/en-us/library/sharepoint/jj164060.aspx.

Upvotes: 5

Related Questions