This sample code demonstrates how to query a Microsoft Office SharePoint Server
2007 or Windows SharePoint Services list from inside SQL Server 2005 or later.
With the growing popularity of SharePoint solutions, SharePoint lists are becoming
an important source of enterprise data. There is no OleDb provider for SharePoint,
so integrating SharePoint data with data in SQL Server can be a challenge.
This CLR User Defined Function shows how you can use the SQL Server's Common
Language Runtime integration to access SharePoint list data through the SharePoint
web services.
Here are some general references for SQL CLR and the SharePoint Web Services.
SharePoint Lists Web Service
http://msdn2.microsoft.com/en-us/library/aa152619.aspx Using CLR Integration in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms345136.aspx Creating CLR Functions
http://msdn2.microsoft.com/en-us/library/ms189876.aspx CREATE ASSEMBLY (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms189524.aspx For detailed instructions on building and installing this sample in SQL Server see the ReadMe.txt in the release.
Then query SharePoint. Something like
select * from dbo.GetListCollection('http://MySharePointSite');
To get the list of SharePoint Lists available. Then retrieve the items for one of the lists.
the GetListItems function returns a single XML document containing all of the items. So to
make use of the data, you would typically use an XML-shreading query like this:
with ListItems as
(
select dbo.GetListItems('http://MySharePointSite','Site Collection Documents',null) AllListItems
)
select
Item.value('@ows_Title', 'varchar(50)') Title,
Item.value('@ows_EncodedAbsUrl','varchar(max)') Url
,Item.query('.') Item
from ListItems cross apply ListItems.AllListItems.nodes('/*/*') Items(Item)
There is also an example of a higher-performance solution that shreads the XML in CLR code
and returns a relational result to SQL Server. But you will need to customize the coding
to return the fields that are relavent in your list.
Select * from dbo.GetListItemsTable('http://MySharePointSite','Site Collection Documents',null)
Please let me know if you find this sample useful, and use the Issue Tracker for any problems or changes you'd like to see.
David Baxter Browne
Microsoft Technology Center - Dallas