Subscribe:      
 

Parsing SharePoint Lists into a SQL view (revisited)

Written By: Rob Fisch -- 4/8/2010 -- join -- contribute -- (2) comments -- printer friendly version

Rating: Rate --

Categories: Integration with other products , MOSS 2007, WSS2, WSS3

< Prev - 1 | 2 | 3 | 4 | 5 | - Next > | Become a paid author

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

< Prev - 1 | 2 | 3 | 4 | 5 | - Next >



Learn more about SharePoint



Sponsor Information




Copyright (c) 2010-2013 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | contribute | feedback | about
Some names and products listed are the registered trademarks of their respective owners.


MSSharePointTips.com | MSSQLTips.com