SQL Server CLR SharePoint Interface

UPDATED RELEASE: SqlClrSharePointInterface Sample Project zipped (Feb 28, 2008)

Mon, 04 Aug 2008 22:03:00 GMT

Here's the initial release of this sample. Please send me feedback if you find it useful or find a problem.

Thanks,

David Baxter Browne
Microsoft Technology Center - Dallas

UPDATED WIKI: Home

dbrowne — Wed, 23 Apr 2008 18:56:31 GMT

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)

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

UPDATED WIKI: Home

dbrowne — Sat, 01 Mar 2008 01:29:53 GMT

select * form dbo.GetListCollection('http://MySharePointSite');

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)

Select * from dbo.GetListItemsTable('http://MySharePointSite','Site Collection Documents',null)

Please let me know if you find this sample useful,

David Baxter Browne
Microsoft Technology Center - Dallas

UPDATED WIKI: Home

dbrowne — Sat, 01 Mar 2008 01:27:11 GMT

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 * form 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,

David Baxter Browne
Microsoft Technology Center - Dallas

UPDATED WIKI: Home

dbrowne — Fri, 29 Feb 2008 22:42:04 GMT

select * form dbo.GetListCollection('http://MySharePointSite');

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)

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

UPDATED WIKI: Home

dbrowne — Fri, 29 Feb 2008 22:29:27 GMT

select * form dbo.GetListCollection('http://MySharePointSite');

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,

David Baxter Browne
Microsoft Technology Center - Dallas

UPDATED WIKI: Home

dbrowne — Fri, 29 Feb 2008 18:23:32 GMT

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 * form 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,

David Baxter Browne
Microsoft Technology Center - Dallas

UPDATED WIKI: Home

dbrowne — Fri, 29 Feb 2008 18:22:53 GMT

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 * form 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 relevent in your list.

Select * from dbo.GetListItemsTable('http://MySharePointSite','Site Collection Documents',null)

Please let me know if you find this sample useful,

David Baxter Browne
Microsoft Technology Center - Dallas

UPDATED RELEASE: SqlClrSharePointInterface Sample Project zipped (Feb 28, 2008)

Fri, 29 Feb 2008 00:57:26 GMT

Here's the initial release of this sample. Please send me feedback if you find it useful or find a problem.

Thanks,

David Baxter Browne
Microsoft Technology Center - Dallas

UPDATED WIKI: Home

dbrowne — Fri, 29 Feb 2008 00:57:06 GMT

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. The code here runs inside your SQL Server and accesses the SharePoint
web service over HTTP, so you will need your DBA's permission and perhaps assistence
to get such a solution running.

The CLR code will, by default, run under the identity of the SQL Server service. If
you want the code to run using the credentials of the client who is connected to SQL
Server instead there is a flag in the code you can change.

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 * form 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 relevent in your list.

Select * from dbo.GetListItemsTable('http://MySharePointSite','Site Collection Documents',null)

Please let me know if you find this sample useful,

David Baxter Browne
Microsoft Technology Center - Dallas

CREATED RELEASE: SqlClrSharePointInterface Sample Project zipped (Feb 28, 2008)

Fri, 29 Feb 2008 00:56:27 GMT

Here's the initial release of this sample. Please send me feedback if you find it useful or find a problem.

Thanks,

David Browne
Microsoft Technology Center - Dallas

UPDATED WIKI: Home

dbrowne — Fri, 29 Feb 2008 00:53:28 GMT

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. The code here runs inside your SQL Server and accesses the SharePoint
web service over HTTP, so you will need your DBA's permission and perhaps assistence
to get such a solution running.

The CLR code will, by default, run under the identity of the SQL Server service. If
you want the code to run using the credentials of the client who is connected to SQL
Server instead there is a flag in the code you can change.

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 * form 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 relevent in your list.

Select * from dbo.GetListItemsTable('http://MySharePointSite','Site Collection Documents',null)

Please let me know if you find this sample useful,

David

UPDATED WIKI: Home

dbrowne — Fri, 29 Feb 2008 00:53:17 GMT

Resource Page Description

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. The code here runs inside your SQL Server and accesses the SharePoint
web service over HTTP, so you will need your DBA's permission and perhaps assistence
to get such a solution running.

The CLR code will, by default, run under the identity of the SQL Server service. If
you want the code to run using the credentials of the client who is connected to SQL
Server instead there is a flag in the code you can change.

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 * form 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 relevent in your list.

Select * from dbo.GetListItemsTable('http://MySharePointSite','Site Collection Documents',null)

Please let me know if you find this sample useful,

David