By: Pavel Pawlowski | Updated: 2013-11-21 | Comments (2) | Related: > SharePoint
Problem
You may need to read SharePoint list or survey's data into SQL Server for further processing. For reading SharePoint lists there are the SharePoint List Source and Destination adapters on CodePlex. The SharePoint Surveys are in fact a list. Unfortunately they are a special kind of list, which are not supported by the adapters provided on CodePlex. In order to read the Surveys data you need to use alternate methods. Check out this tip to learn more.
Solution
This tip will demonstrate an alternate method of loading SharePoint Surveys and Lists data using an SSIS package. We will read the data in XML format, store the data in a table and then query the results using T-SQL.To retrieve the XML data we will utilize SharePoint Team Services and in particular we will use the owssvr.dll ISAPI extension which implements the Team Services functionality.
Getting Information about the Survey or List
As mentioned above we will use the owssvr.dll ISAPI extension to get the Survey or List data in XML format though the URL Protocol. To be able to use it, we need the URL of the SharePoint application and GUID of the list. The easiest way to retrieve this information is to use the "Export to Spreadsheet" function as shown below on the individual survey.
This process generates an owssvr.iqy file which can be used to get the data into Excel, but also contains all the information we need. In this file, you can find the SharePointApplication URL and also the SharePointListName which is the GUID of the list we need.
WEB
1
https://www.mydomain.com/8/FeedbackPE/_vti_bin/owssvr.dll?XMLDATA=1&List={7975F329-B54D-43AD-B0A8-4AE071EC59A5}&View={C990B331-BEB0-4B9A-A41D-83C3616E19EB}&RowLimit=0&RootFolder=%2f8%2fFeedbackPE%2fLists%2fFeedback
Selection={7975F329-B54D-43AD-B0A8-4AE071EC59A5}-{C990B331-BEB0-4B9A-A41D-83C3616E19EB}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=https://www.mydomain.com/8/FeedbackPE/_vti_bin
SharePointListView={C990B331-BEB0-4B9A-A41D-83C3616E19EB}
SharePointListName={7975F329-B54D-43AD-B0A8-4AE071EC59A5}
RootFolder=/8/FeedbackPE/Lists/Feedback
Building the SSIS Data Flow
The data flow is quite simple. We will use Script Component as Source and OLEDB as destination. The Script Component retrieves the data from SharePoint and the OLEDB Destination stores it into a working table. For more information about SSIS check out this tutorial and see the sample SSIS Package at the end of this tip.
The Script Component will have a single output column ListXMLData of Unicode text stream (DT_NTEXT) data type and we will pass two project parameters ListURL and ListGUID as ReadOnlyVariables.
Once the column and variables are defined, we can write the script to get the XML Data. The script will first build the URL according the URL Protocol and then invoke HttpWebRequest to get the XML data.
If we use the https protocol to access the SharePoint site, then it will probably be necessary to assign a custom certificate validation method to ServicePointManager, otherwise the site certificate will not be validated and the http request will fail.
Please also note, that in the case of Surveys, you will have to be a Survey administrator to be able to read all the responses. Without administrator rights on the Survey, you will not be able to retrieve all responses.
For the complete data retrieval we implement the internal SharePointList Class and then call the SharePointList.GetSharePointListXml() method in the CreateNewOutputRows() method of the Script Component. The GetSharePointListXML method returns the List XML data as string, which we pass as output to parse for our final table.
internal class SharePointList
{
/// <summary>
/// Reads the SharePoint List XML Data
/// </summary>
/// <param name="siteUrl">Site URL</param>
/// <param name="listGuid">GUID of the List</param>
/// <returns>SharePoint List XML Data</returns>
public static string GetSharePointListXml(string siteUrl, string listGuid)
{
return GetSharePointListViewXml(siteUrl, listGuid, null, null);
//return doc.OuterXml;
}
/// <summary>
/// Reads the SharePoint List XML Data
/// </summary>
/// <param name="siteUrl">Site URL</param>
/// <param name="listGuid">GUID of the List</param>
/// <param name="viewGuid">GUID of the View</param>
/// <param name="fieldsList">List of the Fields to retrieve</param>
/// <returns>SharePoint List XML Data</returns>
public static string GetSharePointListViewXml(string siteUrl, string listGuid, string viewGuid, string fieldsList)
{
StringBuilder urlBuilder = new StringBuilder(siteUrl.Length + listGuid.Length + (viewGuid == null ? 0 : viewGuid.Length) + (fieldsList == null ? 0 : fieldsList.Length) + 100);
//Add Site and the Display Command to URL and the List GUID
urlBuilder.AppendFormat("{0}{1}/owssvr.dll?Cmd=Display&List={2}", siteUrl, siteUrl.EndsWith("/") ? "" : "/", QuoteWithBrackets(listGuid));
//Add View guid into the URL if provided
if (!string.IsNullOrEmpty(viewGuid))
urlBuilder.AppendFormat("&View={0}", QuoteWithBrackets(viewGuid));
urlBuilder.Append("&XMLDATA=TRUE"); //This instruct the owssvr.dll to return XML data we need.
//Add output columns or * representing all columns (if no columns list is provided) into the URL
urlBuilder.AppendFormat("&Query={0}", string.IsNullOrEmpty(fieldsList) ? "*" : fieldsList);
return GetSharePointListUrlXml(urlBuilder.ToString());
}
/// <summary>
/// Reads the SharePoint List XML Data
/// </summary>
/// <param name="listUrl">ULR of the List to read data</param>
/// <returns>SharePoint List XML Data</returns>
public static string GetSharePointListUrlXml(string listUrl)
{
WebResponse response;
//Use our internal class which always returns true to certifiicate validation so we always trust our site
ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(CertificateOverride.RemoteCertificateValidationCallback);
//Web Request to the SharePoint URL to retrieve the Surveys Content
HttpWebRequest request = (HttpWebRequest)System.Net.HttpWebRequest.Create(listUrl);
//We need to assign a cookie container to the HttpWebRequest as withouth cookies the request agains SharePoint will not work.
CookieContainer cookieContainer = new CookieContainer();
request.CookieContainer = cookieContainer;
//We are doing the request udenr the credential of the user running the package.
request.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
//Get the response and read the whole content (xml data)
response = request.GetResponse();
string xmlData;
using (StreamReader sr = new StreamReader(response.GetResponseStream()))
{
xmlData = sr.ReadToEnd();
sr.Close();
}
return xmlData;
}
/// <summary>
/// Adds brackets around the input string if the brackets are missing
/// </summary>
/// <param name="input">input string</param>
/// <returns>output string with brackets on the beginning and end</returns>
private static string QuoteWithBrackets(string input)
{
string output = input;
if (!output.StartsWith("{"))
output = "{" + output;
if (!output.EndsWith("}"))
output += "}";
return output;
}
/// <summary>
/// Internal class with the callback method for certificate validation. We return true, so we trust our site without any
/// additional validation
/// </summary>
internal class CertificateOverride
{
public static bool RemoteCertificateValidationCallback(object sender, X509Certificate certificate, X509Chain chaing, SslPolicyErrors sslPolicyErrors)
{
return true;
}
}
}
Processing the XML data
The XML data we retrieve and store in the table will look like the sample below (sample output is shortened). As we can see that at the beginning there is the row schema definition followed by the actual data.
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">
<s:AttributeType name="ows_ContentTypeId" rs:name="Content Type ID" rs:number="1">
<s:datatype dt:type="int" dt:maxLength="512" />
</s:AttributeType>
<s:AttributeType name="ows_Who_x0020_is_x0020_the_x0020_per" rs:name="Who has given the response" rs:number="4">
<s:datatype dt:type="string" dt:lookup="true" dt:maxLength="512" />
</s:AttributeType>
<s:AttributeType name="ows_What_x0020_is_x0020_the_x0020_pr" rs:name="What is the project or service this feedback concerns?" rs:number="5">
<s:datatype dt:type="string" dt:maxLength="1073741823" />
</s:AttributeType>
<s:AttributeType name="ows_Last_x0020_Modified" rs:name="Modified" rs:number="31">
<s:datatype dt:type="datetime" dt:lookup="true" dt:maxLength="8" />
</s:AttributeType>
<s:AttributeType name="ows_Created_x0020_Date" rs:name="Created" rs:number="32">
<s:datatype dt:type="datetime" dt:lookup="true" dt:maxLength="8" />
</s:AttributeType>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row ows_ContentTypeId="0x0029F375794DB5AD43B0A84AE071EC59A5" ows_Who_x0020_is_x0020_the_x0020_per="2381;#[email protected]" ows_What_x0020_is_x0020_the_x0020_pr="Project1" ows_Last_x0020_Modified="8;#2013-06-12 15:31:42" ows_Created_x0020_Date="8;#2013-06-12 15:31:42" />
<z:row ows_ContentTypeId="0x0029F375794DB5AD43B0A84AE071EC59A5" ows_Who_x0020_is_x0020_the_x0020_per="2572;#[email protected]" ows_What_x0020_is_x0020_the_x0020_pr="Project2" ows_Last_x0020_Modified="9;#2013-06-12 15:40:06" ows_Created_x0020_Date="9;#2013-06-12 15:40:06" />
<z:row ows_ContentTypeId="0x0029F375794DB5AD43B0A84AE071EC59A5" ows_Who_x0020_is_x0020_the_x0020_per="1936;#[email protected]" ows_What_x0020_is_x0020_the_x0020_pr="Project3" ows_Last_x0020_Modified="10;#2013-06-12 15:58:21" ows_Created_x0020_Date="10;#2013-06-12 15:58:21" />
</rs:data>
</xml>
Based on the schema and XML structure, we can invoke a T-SQL query against the XML data to get individual feedback records as separate rows.
WITH XMLNAMESPACES('urn:schemas-microsoft-com:rowset' AS rs, '#RowsetSchema' AS z)
SELECT
T.RowData.value('(@ows_Who_x0020_is_x0020_the_x0020_per)[1]', 'varchar(255)') AS WhoHasGivenFeedback
,T.RowData.value('(@ows_What_x0020_is_x0020_the_x0020_pe)[1]', 'varchar(128)') AS Project
,T.RowData.value('(@ows_Created)[1]', 'datetime') AS CreatedTMS
,T.RowData.value('(@ows_Modified)[1]', 'datetime') AS ModifiedTMS
FROM dbo.ListXmlData ld
CROSS APPLY ld.ListXmlData.nodes('/xml/rs:data/z:row') T(RowData)
It produces a result like the one below. This output can be stored in your final table for further processing.
WhoHasGivenFeedback Project CreatedTMS ModifiedTMS ------------------------- --------- ----------------------- ----------------------- 2381;#[email protected] Project1 2013-06-12 09:54:34.000 2013-06-12 09:54:34.000 2572;#[email protected] Project2 2013-06-12 10:41:10.000 2013-06-12 10:41:10.000 1936;#[email protected] Project3 2013-06-12 15:25:22.000 2013-06-12 15:25:22.000
Next Steps
- On MSDN you can take a look at the Overview of the SharePoint Team Services Architecture.
- Here are details of the URL Protocol.
- On MDSN you can also find details of the Display method used to extract the XML data from SharePoint.
- Details on querying XML data in SQL Server can be found in Introduction to XQuery in SQL Server 2005 and xml Data Type Methods
- You can download complete sample SSIS 2012 project here.
About the author
Pavel Pawlowski is Lead BI Specialist in Tieto with focus on Microsoft SQL Server and Microsoft BI platform.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-11-21
