Parsing SharePoint Lists into a SQL view (revisited)

By: Rob Fisch | Comments | Related: > SharePoint

Problem

Sharepoint is a great collaborative tool, but its internal reporting capability is limited. Wouldn't it be so powerful if you could report against one or more Sharepoint lists from Microsoft's Reporting Services?

Solution

A few years ago, I wrote an article demonstrating how to create a SQL view out of a SharePoint list for the purposes of reporting (i.e. with Reporting Services). The procedure demonstrates how to manually parse the tp_fields column in the SharePoint database table (WSS2) or view (WSS3) called "Lists".

While that is a great exercise in understanding how the xml in the field is put together, a while later, I learned of a much easier technique using XML Notepad.

To keep it simple, all SharePoint list data in the site collection is stored in a single table (WSS2) or view (WSS3/MOSS2007) called "UserData". The SQL field names are generic, so you have to do a little detective work to map the generic field names to the SharePoint column names. This mapping is stored in XML format in a field called tp_fields.

Read on to learn how to do this...

Preparation

If you don't already have it, download and install XML Notepad.

Search the Microsoft Downloads page for "XML Notepad", and you will find the most recent version of that product (currently XML Notepad 2007). It is a free product. Download and install the package. It is a straight forward installation with nothing to choose other than where to install it.

Finding The Target List

First, I find the list I would like to parse by running the following query.

Query #1

SELECT Webs.Title AS SiteName, Lists.tp_Title AS ListName, Lists.tp_ID
FROM Lists 
INNER JOIN Webs ON Lists.tp_WebId = Webs.Id
ORDER BY SiteName, ListName

Once I choose a list, I can simplify the query before going forward using the tp_ID field. I also add the 'tp_Field' column to the query, which I'll need later.

Query #2

SELECT tp_Title AS ListName, tp_ID, tp_Fields
FROM Lists
WHERE (tp_ID = '155AEF7B-919D-4C1E-8EB8-0DF47BD83FF4')

The ID above (155AEF7B-919D-4C1E-8EB8-0DF47BD83FF4) is my list. You will have to find your own.

The results of Query #2 above shows this.

Using The Results

Next, I open a text editor (like Windows Notepad), and enter the following:

(The XML stored in the tp_Fields column is stored without a set of root tags, so I add them.)

Between the and tags, I paste the contents of the tp_Fields column returned in Query #2.

The result looks something like this:

<root>

12.0.0.6421.0.0<FieldRef Name="Title" ColName="nvarchar1"/><FieldRef 
Name="Comment" ColName="ntext2"/><FieldRef Name="Priority" ColName="nvarchar4"/><Field 
Type="Note" DisplayName="Progress Notes" Required="FALSE" NumLines=



etc...

Then I remove the "12.0.0.6421.0.0" at the begining.

The full result is as follows. Don't spend any time trying to make sense of the code...that's what we're using XML Notepad for. I only include it in it's entirety in case you want to use it with your own copy of XML Notepad.

<root>

<FieldRef Name="Title" ColName="nvarchar1"/><FieldRef 
Name="Comment" ColName="ntext2"/><FieldRef Name="Priority" ColName="nvarchar4"/><Field 
Type="Note" DisplayName="Progress Notes" Required="FALSE" NumLines="6" RichText="TRUE" 
RichTextMode="FullHtml" IsolateStyles="TRUE" Sortable="FALSE" 
ID="{b3fb378f-765e-4400-9846-dbe3554d6b46}" SourceID="{155aef7b-919d-4c1e-8eb8-0df47bd83ff4}" 
StaticName="Progress_x0020_Notes" Name="Progress_x0020_Notes" ColName="ntext3" 
RowOrdinal="0"/><Field ID="{53101f38-dd2e-458c-b245-0c236cc13d1a}" Type="User" 
List="UserInfo" Name="AssignedTo" DisplayName="Assigned To" SourceID="http://schemas.microsoft.com/sharepoint/v3" 
StaticName="AssignedTo" ColName="int2" Required="FALSE" Group="" ShowField="ImnName" 
UserSelectionMode="PeopleAndGroups" UserSelectionScope="46" Version="1" 
RowOrdinal="0"/><Field ID="{3f277a5c-c7ae-4bbe-9d44-0456fb548f94}" Type="Choice" 
Name="Status" DisplayName="Issue Status" SourceID="http://schemas.microsoft.com/sharepoint/v3" 
StaticName="Status" ColName="nvarchar3" Required="FALSE" FillInChoice="FALSE" 
Format="Dropdown" Version="1" RowOrdinal="0"><CHOICES><CHOICE>Not 
Started</CHOICE><CHOICE>In 
Progress</CHOICE><CHOICE>Deferred</CHOICE><CHOICE>Completed</CHOICE></CHOICES><Default>Not 
Started</Default></Field><Field ID="{6df9bd52-550e-4a30-bc31-a4366832a87d}" 
FromBaseType="TRUE" Type="Choice" Name="Category" DisplayName="Category" 
SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="Category" 
ColName="nvarchar5" Required="FALSE" FillInChoice="FALSE" Format="Dropdown" 
Version="1" RowOrdinal="0"><CHOICES><CHOICE>Workstation</CHOICE><CHOICE>File 
Server</CHOICE><CHOICE>Network</CHOICE><CHOICE>Printer</CHOICE><CHOICE>Software</CHOICE><CHOICE>Remote 
Access</CHOICE><CHOICE>Other</CHOICE></CHOICES></Field><FieldRef Name="DueDate" 
ColName="datetime1"/><FieldRef Name="ContentTypeId"/><FieldRef Name="_ModerationComments" 
ColName="ntext1"/><FieldRef Name="RelatedID" ColName="uniqueidentifier1"/><FieldRef 
Name="RelatedIssue" ColName="int1"/><FieldRef Name="File_x0020_Type" ColName="nvarchar2"/><Field 
DisplayName="Notify Helpdesk Manager" Type="WorkflowStatus" Required="FALSE" 
ID="{530d675b-3ab9-40b6-b3fb-5d851b95702f}" SourceID="{155aef7b-919d-4c1e-8eb8-0df47bd83ff4}" 
StaticName="NotifyHe" Name="NotifyHe" ColName="nvarchar6" RowOrdinal="0" 
Version="10" WorkflowStatusURL="_layouts/WrkStat.aspx" ReadOnly="TRUE"><CHOICES><CHOICE>Starting</CHOICE><CHOICE>Failed 
on Start</CHOICE><CHOICE>In Progress</CHOICE><CHOICE>Error 
Occurred</CHOICE><CHOICE>Canceled</CHOICE><CHOICE>Completed</CHOICE><CHOICE>Failed 
on Start (retrying)</CHOICE><CHOICE>Error Occurred 
(retrying)</CHOICE><CHOICE/><CHOICE/><CHOICE/><CHOICE/><CHOICE/><CHOICE/><CHOICE/><CHOICE>Stopped</CHOICE></CHOICES></Field><Field 
DisplayName="Progress Updates" Type="WorkflowStatus" Required="FALSE" 
ID="{a7c95fb7-6513-4704-9ab4-aba7a4fec705}" SourceID="{155aef7b-919d-4c1e-8eb8-0df47bd83ff4}" 
StaticName="Progress" Name="Progress" ColName="nvarchar7" RowOrdinal="0" 
Version="10" WorkflowStatusURL="_layouts/WrkStat.aspx" ReadOnly="TRUE"><CHOICES><CHOICE>Starting</CHOICE><CHOICE>Failed 
on Start</CHOICE><CHOICE>In Progress</CHOICE><CHOICE>Error 
Occurred</CHOICE><CHOICE>Canceled</CHOICE><CHOICE>Completed</CHOICE><CHOICE>Failed 
on Start (retrying)</CHOICE><CHOICE>Error Occurred 
(retrying)</CHOICE><CHOICE/><CHOICE/><CHOICE/><CHOICE/><CHOICE/><CHOICE/><CHOICE/><CHOICE>Stopped</CHOICE></CHOICES></Field><Field 
Name="V3Comments"/><Field Name="RelatedIssues"/>

</root>

Using XML Notepad

Now I open XML notepad. It's not all that obvious, but I copy the code from Windows Notepad ("root" tags and all) into a blank area of XML Notepad. I am not sure if it matters exactly where you 'paste', but I do it in the "Tree View" window on the left.

Afterwards, the XML Notepad window looks as follows:

Open some of the nodes and we can see how the SharePoint "Name" (node) maps to the SQL "ColName" (node).

Parsing several desired fields this way, I come up with the following SQL statement. The statement uses the "ColName" node as the SQL field, and the "Name" (or DisplayName) node an an alias for the SQL field.

SELECT nvarchar1 AS Title, nvarchar4 AS Priority, datetime1 AS [Due Date], nvarchar3 AS [Issue Status] 
FROM UserData 
WHERE (tp_ListId = '155AEF7B-919D-4C1E-8EB8-0DF47BD83FF4')

SQL Results

After executing the SQL statement the results of the code looks like this:

The SharePoint List looks like this:

With XML Notepad (a free tool), we can easily parse SharePoint lists, making SQL queries that can be used as a source for other types of reporting.

Next Steps

Check out MSSQLTips.com for great information about Microsoft SQL Server.

About the author

Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips