At least once in your life as Business Intelligence developer, you will have to face the challenge to read some list that is stored inside Sharepoint.
Looking back at the version 2010, it was pretty easy to select a table named “AllUserData” and then figuring out what is the right column between nvarchar1 and nvarchar50. However, in Sharepoint 2016 and 2016, this direct access hasn’t been supported by Microsoft anymore.
Nevertheless, in this short article I will show you how we can continue selecting data from sharepoint’s lists. 🙂
Just to let you know, there are some cons of selecting data directly from Sharepoint database:
- It is completely unsupported by Microsoft.
- The schema may change if the Sharepoint is updated
- Performance issues, such as: lock, transaction, I/O
Anyway, here is a simple way to get your precious data:
1. To avoid performance issues, isolate your list to another table:
if object_id(‘List1’) is not null
drop table List1
from [WSS_Content].[dbo].[AllUserData] where tp_listid = ‘2A8B1218-FA69-4641-9205-166EAAE78405’
2. The tp_ListId column is stored as XML, it means that you have to split the XML.
x.c.value(‘(float1/text())’, ‘FLOAT’) AS float1,
x.c.value(‘(float2/text())’, ‘FLOAT’) AS float2,
x.c.value(‘(float3/text())’, ‘FLOAT’) AS float3,
x.c.value(‘(ntext2/text())’, ‘NVARCHAR(MAX)’) AS ntext2,
x.c.value(‘(ntext3/text())’, ‘NVARCHAR(MAX)’) AS ntext3,
x.c.value(‘(nvarchar1/text())’, ‘NVARCHAR(MAX)’) AS nvarchar1,
x.c.value(‘(nvarchar2/text())’, ‘NVARCHAR(MAX)’) AS nvarchar2,
x.c.value(‘(nvarchar3/text())’, ‘NVARCHAR(MAX)’) AS nvarchar3,
x.c.value(‘(nvarchar4/text())’, ‘NVARCHAR(MAX)’) AS nvarchar4,
x.c.value(‘(nvarchar5/text())’, ‘NVARCHAR(MAX)’) AS nvarchar5
FROM ( SELECT CAST( tp_columnset AS XML ) tp_columnset, tp_ID,tp_ListId FROM List1 ) t
CROSS APPLY t.tp_columnset.nodes(‘.’) x(c)
- Insert some rows in your list and validate if you have chosen the right columns in your select, otherwise you will deliver wrong data for your users.
- Don’t forget to isolate your table, avoid locking sharepoint tables.
- There are some other options of integrations that you can do, for example, this documentation is about one SSIS plugin developed in 2011.