Extract List Column Data From Sharepoint 2010 ows_MetaInfo

All of the list attributes (columns) can be found in the ows_MetaInfo attribute. This is just a huge string packed with information that you can parse to extract. So far I’ve been able to gather the following about this data:

  1. Keys that start with “vti_*****” are predefined list attributes (e.g. vti_title, vti_author, vti_modifiedby, etc)
  2. Keys that start with “_dlc_****” contain data about the resource itself (e.g _dlc_DocIdUrl, _dlc_DocIdItemGuid, etc)
  3. Keys that are the exact name of an attribute are columns that you added to the list after creation. (e.g. My Custom Cost)

To extract any of this data I use the following to regular expressions functions:


// First get the key / value you want. In this instance I want the custom "title" column I added to the list.
preg_match('/(?=vti_title).*/', $result->getAttribute("ows_MetaInfo"), $match); // Returns "vti_title:RW|my title is this: what?"

// Extract the value
$title = preg_replace('/w*:w{2}|/', '', $match[0]); // Returns "my title is this: what?"

Displaying and Downloading Sharepoint Document Libraries Using PHP

Objective

Create a PHP based web page that creates a list of all documents in a SharePoint 2010 Document Library. Each item in this list is a link to the actual document. When a user clicks the link, the document is downloaded from SharePoint’s database and displayed to the user.

Background

Sharepoint 2010 is fantastic. If you don’t use it, look into it. (Drupal isn’t bad either). While i’ve been migrating a custom coded CMS to Sharepoint 2010, I keep running across interesting issues. My latest was that I needed to let users submit PDF documents to the system and then have those documents display on the website.

With a traditional PHP website, piece of cake: Save document to server, list link to file, user clicks link.

SharePoint however stores all of the documents into a database that is only understandable by SharePoint…

Prep

Make sure you have done the following:

  1. Enabled Basic Authentication on the SharePoint server
  2. Enabled the openssl, curl and soap PHP extensions on your web server
  3. Downloaded a copy of the Lists services to your web site. (Go to https://sharepointsite/subsite_if_one/_vti_bin/Lists.asmx?WSDL)

Now on to the coding!

Steps

Here is my main loop that fetches the document list and creates links to the document on the page:


$results = getDocumentList({GUID});
$load = false;
$out = '

    ';
    //Fetching the elements values. Specify more attributes as necessary
    foreach($results as $result){

    // Get a custom column in my list.
    $title = getMetaItem($result, 'vti_title');

    // Get the document url and guid of document
    $docURL = getMetaItem($result, '_dlc_DocIdUrl');
    $docURL = preg_split('/w*|/', $docURL);
    $docURL = explode(",", $docURL[0]); // Parse the full url, doc_id

    $out .= "

  • {$title}
  • ";

    if(isset($_GET['id']) && trim($docURL[1]) == $_GET['id']){
    $load = true;
    $url = "https://usportal.aaalliance.ucf.edu/" . rawurlencode(preg_replace('/(^[0-9]*).{2}/', '', $result->getAttribute("ows_FileRef")));
    }
    }
    $out .= '

';

// If requesting document
//preg_match('/(dw)*-(dw)*-(dw)/', $_GET['id'])){
if($load){
openDocument($url);
}
else echo $out;

My function to get all of the documents in a specified library:

/*
* @param $listName string this is the GUID of your library. To find it go to your library through the sharepoint website. Then click on "library settings". Once there look at the URL. It should be: https://sharepointsite/subsite/_layouts/listedit.aspx?List={E2EAE561-D43R-4F8B-AC4D-146BEB66DBD4}

Use what List= is (including the {})
*/
function getDocumentList($listName, $config = array())
{
$authParams = getAuth(); // returns username:password

/* A string that contains either the display name or the GUID for the list.
* It is recommended that you use the GUID, which must be surrounded by curly
* braces ({}).
*/
$rowLimit = '150';

/* Local path to the Lists.asmx WSDL file (localhost). You must first download
* it manually from your SharePoint site (which should be available at
* yoursharepointsite.com/subsite/_vti_bin/Lists.asmx?WSDL)
*/
$wsdl = $_SERVER['DOCUMENT_ROOT']."/Lists.xml";

//Creating the SOAP client and initializing the GetListItems method parameters
$soapClient = new SoapClient($wsdl, $authParams);
$params = array('listName' => $listName,
'rowLimit' => $rowLimit,
'viewFields' => ""
);

//Calling the GetListItems Web Service
$rawXMLresponse = null;
try{
$rawXMLresponse = $soapClient->GetListItems($params)->GetListItemsResult->any;
}
catch(SoapFault $fault){
//var_dump($fault);
echo 'Fault code: '.$fault->faultcode;
echo 'Fault string: '.$fault->faultstring;
}

//Loading the XML result into parsable DOM elements
$dom = new DOMDocument();
$dom->loadXML($rawXMLresponse);

$results = $dom->getElementsByTagNameNS("#RowsetSchema", "*");
unset($soapClient);

return $results;
}

My function to parse ows_MetaInfo

/*
* Parse ows_MetaInfo for a value
*/
function getMetaItem($result, $key)
{
$match = array();
preg_match('/(?='.$key.').*/', $result->getAttribute("ows_MetaInfo"), $match);
return preg_replace('/w*:w{2}|/', '', $match[0]);
}

Finally, open the document with by it’s URL


function openDocument($url)
{
$authParams = getAuth();

// Get the file name.
$filename = substr(strrchr(rawurldecode($url), '/'), 1);
header("Content-Disposition: attachment; filename=".str_replace(' ', '', (urlencode($filename))));
header("Content-type: application/octet-stream");
header("Content-Transfer-Encoding: binary");

$ch = curl_init();

curl_setopt($ch, CURLOPT_BINARYTRANSFER, true);
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_USERPWD, $authParams['login'] . ":" . $authParams['password']);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
//curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2); // Not working for me
//curl_setopt($ch, CURLOPT_CAINFO, getcwd() . "/../data/CAcerts/my.pem");

$response = curl_exec($ch);
echo $response;
curl_close($ch);
}

Oh how I love making systems talk to systems…

Thank you to http://davidsit.wordpress.com/2010/02/23/reading-a-sharepoint-list-with-php/ for getting me started on how to read sharepoint lists.