Wednesday, September 16, 2009

I recently had the need to run some SQL queries on data from SharePoint Lists. I knew that all the list data was stored in the AllUserData table (filtered by the UserData view). Unfortunately the actual list values are stored in a collection of generically named columns ( like bit1..bit16, datetime1..datetime8, float1..float12, int1..int16, ntext1..ntext32, nvarchar1..nvarchar64 and sql_variant1..sql_variant8 ). I knew I could figure out what column contained what values by looking at the data, but I had to run queries on too many lists. I looked at the AllLists table (filtered by the Lists view) and noticed the column tp_Fields. This contained an xml document that described the list fields and which generic column name (in the UserData table) contained the data. After stripping off the leading version number and wrapping it in a "root" xml tag, I was able to parse the column/field map and build a dynamic query for the data. Below is a section of the code I used to perform this.

DECLARE @COLUMN_DETAIL_XML AS VARCHAR(MAX)
DECLARE @LIST_GUID AS VARCHAR(50)
DECLARE @IDOC INT
DECLARE @SQL VARCHAR(MAX)

SELECT
@COLUMN_DETAIL_XML = TP_FIELDS,
@LIST_GUID = TP_ID
FROM
LISTS LISTS
INNER JOIN
WEBS WEBS
ON
LISTS.TP_WEBID = WEBS.ID
WHERE
LISTS.TP_TITLE = 'Announcements'
AND
WEBS.FULLURL = '' -- root web site

-- add root node for "proper xml" and remove initial version stamp
SET @COLUMN_DETAIL_XML = '<root>' + SUBSTRING(@COLUMN_DETAIL_XML , CHARINDEX('<', @COLUMN_DETAIL_XML ), 99000) + '</root>'

EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT, @COLUMN_DETAIL_XML

SELECT
*
INTO
#COLUMNS
FROM
(
SELECT
COLNAME,
NAME
FROM
OPENXML ( @idoc, '/root/Field', 3)
WITH (
AllowHyperlink varchar(1000),
AppendOnly varchar(1000),
ColName varchar(1000),
Description varchar(1000),
DisplaceOnUpgrade varchar(1000),
DisplayName varchar(1000),
Format varchar(1000),
FromBaseType varchar(1000),
[Group] varchar(1000),
ID varchar(1000),
IsolateStyles varchar(1000),
List varchar(1000),
Mult varchar(1000),
Name varchar(1000),
NumLines varchar(1000),
Percentage varchar(1000),
PrependId varchar(1000),
ReadOnly varchar(1000),
Required varchar(1000),
RichText varchar(1000),
RichTextMode varchar(1000),
RowOrdinal varchar(1000),
Sealed varchar(1000),
ShowField varchar(1000),
Sortable varchar(1000),
SourceID varchar(1000),
StaticName varchar(1000),
Type varchar(1000),
UnlimitedLengthInDocumentLibrary varchar(1000),
UserSelectionMode varchar(1000),
UserSelectionScope varchar(1000),
Version varchar(1000),
WorkflowStatusURL varchar(1000)
)
UNION
SELECT
COLNAME,
NAME
FROM
OPENXML ( @idoc, '/root/FieldRef', 3)
WITH (
ColName varchar(1000),
Name varchar(1000),
SourceID varchar(1000),
StaticName varchar(1000)
)
) G

EXEC SP_XML_REMOVEDOCUMENT @IDOC

--SELECT * FROM #COLUMNS

SET @SQL =
'SELECT
USERDATA.TP_ID AS ID,'


SELECT @SQL = @SQL + ' [USERDATA].[' + COLNAME + '] AS [' + NAME +
'],
'

FROM
#COLUMNS G
WHERE
COLNAME IS NOT NULL AND NAME IS NOT NULL

SET @SQL = LEFT(@SQL, LEN(@SQL) - 3) +
'
FROM
USERDATA USERDATA
INNER JOIN
LISTS LISTS
ON
USERDATA.TP_LISTID = LISTS.TP_ID
INNER JOIN
WEBS WEBS
ON
LISTS.TP_WEBID = WEBS.ID
WHERE
LISTS.TP_ID = '''
+ @LIST_GUID + ''''
EXEC (@SQL)

DROP TABLE #COLUMNS