Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Parsing SharePoint Lists into a SQL view (revisited)


By:   |   Read Comments   |   Related Tips: > 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:

<root>
</root>

(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.


Last Update:






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

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools