XML and XSLT in SharePoint Case Study

on Tuesday, 17 May 2011. Posted in How To

The Scenario:

You have an instance of SharePoint running locally and you are tasked with pulling information from a third party host via API and displaying the results in a SharePoint page and have the data being pulled update in real time.

Background and Solution:

API stands for Application Programming Interface.  When using APIs to talk about web services, an API is just an interface that allows other software to use some of its services.  To put it another way, an API for a web service gives end users the ability to tap into that service so they can use it on their own site or software.  Lets say you wanted to add the power of Google’s search bar to your own website.  Google provides an API that lets you leverage search bar on your site, so you can search through all your own content using the sophisticated algorithms of Google search, without needing to understand how any of these algorithms work.  That's basically what all APIs are like.  They're a list of functions a software provider offers that lets you leverage their coding power in your own applications.  If you've ever seen Google maps customized to show a certain area or with certain graphics, or in some other unique way while still having all the power of regular Google maps--you're looking at the end results of someone using Google’s map API.

What does an API look like?  How do I use one?

Companies like Google and yahoo and countless others that provide APIs you can use, do so the same way.  First, you create an account with them, and second you request an API key.  This API Key is basically a single string that companies provide you that will give you access to their APIs--sort of like your login and password all rolled up into one.  The string could just be a bunch of numbers and letters to you, but to the API provider, it's how they can identify you and know which APIs you have rights to, which ones you don't, and what kind of info you're allowed to be pulling back.

There are two common methods for using an API string, both relying on HTML Posts and Gets.  Posts allow you to send data too the outside server, and gets allow you to pull data from it.  In this scenario, you're asked to pull data from a third party host, so you need to get that information.  A common way to do that is to make a request via the URL for that API's data and include in that request your API key, API method, and whatever other parameters are required.

Here's what a typical API call might look like:

http://www.somebusinesswebsite.com/?f=get.friends.list&key=4389032304932893AFA

If you pop an API call like that into your URL (don't use this one--it won't work), it should bring you back results in XML format.

Let's break down this URL:

http://www.somebusinesswebsite.com - the website you have an API key for.
?f=get.friends.list - the method or function you are calling from the website.  The name of this method and how it's used (whether it's "f=" or "m=" or whatever) is determined by the website you have the API key for.
&key=4389032304932893AFA - where you also send your API key--essentially your credentials for calling the information.

Remember, when sending info via the GET method, all your variables follow an initial question mark (?) and are separated by ampersands (&).  Their actual values are determined by what appears after the equal sign for each.

Assuming your API call worked correctly in the URL, you should be returned with the information you requested in XML format.

XML stands for eXtensible Markup Language.  It is a common denominator for most data transfers across the internet.  Using customized tags that are defined by a Document Type Definition (DTD), it allows data to be read by interpreters in the format it was intended.  As long as the interpreters know the definition of each of the tags--as determined in the DTD--then each of the elements within those tags can be accurately transformed into whatever way the end user wants.

Continuing the example, let's say the API call returned this XML document:


<MEMBER>
<FIRST_NAME>Bob</FIRST_NAME>
<LAST_NAME>Jones</LAST_NAME>
<ADDRESS>55 Main Street</ADDRESS>
</MEMBER>
<MEMBER>
<FIRST_NAME>Alice</FIRST_NAME>
<LAST_NAME>Smith</LAST_NAME>
<ADDRESS>86 Oak Drive</ADDRESS>
</MEMBER>

The tags "member", "first_name", "last_name" and "address" are returned with content for two users: Bob Jones and Alice Smith.  You can see how the information is separated out and structured.  The data is there, but there's one big problem: it's really hard to read.  There has to be a way of taking that data and transforming it into a table or some other format that is easier on the eyes.  And of course, there is. 

XSLT stands for Extensible Stylesheet Language Transformations and is the language used to convert XML documents into HTML.  This is a language with a lot of options and power to not only display your XML in a more readable format, but to do a number of other cool XML transformations.  Visit the XSLT Tutorial for more information about how to use it.

In this example, we want to take our XML and make it into readable HTML, like in an HTML table.

To do this, we have to transform the XML in HTML via XSLT.  Here is an example of an XSLT document that will transform our sample XML into an HTML table:


<?xml version="1.0" encoding="ISO-8859-1" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<h2>API Test Pull for Friend's List</h2>
<table border="1">
<tr bgcolor="#9acd32">
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
</tr>
<xsl:for-each select="MEMBER">
<tr>
<td>
<xsl:value-of select="FIRST_NAME" />
</td>
<td>
<xsl:value-of select="LAST_NAME" />
</td>
<td>
<xsl:value-of select="ADDRESS" />
</td>
</td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

You'll notice in this example that there's both HTML tags and XSL tags mixed together.  This is exactly how it should look since XSLT is the middle-man between the two types of output.  In the example, the XSLT pulls the values from each block of code inside the "MEMBERS" tag and adds them as cells within a table.  The column names for these cells are hard coded in the HTML.

In the XSLT tutorial you can immediate see the effects of this code on the XML.  Using the above code, our HTML output will now look like this:

API Test Pull for Friend's List

First Name Last Name Address
Bob Jones 55 Main Street
Alice Smith 86 Oak Drive


We have all the pieces now of using an API call to dynamically populate an HTML table, so the final piece of the puzzle is storing this in a system that understands how to pull XML and XSLT and transform it at once.

This can be done in several ways, however if you plan to use Microsoft SharePoint to host this info, it is especially easy.  SharePoint (2007) has a webpart called “XML Web Part” that takes two external inputs: an XML link (or file) and an XSL file.  Add this web part to whatever page you want to display the Friend’s List and fill in the parameters.  It will look something like this:


Notice in the XML Link area, I pasted the API call.  By using a URL instead of some local XML document, I’m sure whenever I load the page that I am getting the most up-to-date XML.  This essentially makes the XML dynamic for every time the page is loaded.

In the XSL Link text area, I pointed the URL to a local file where I stored the XSLT file that I wrote (called FriendsList.xsl).  Note: I did skip a step where I saved the XSLT file to SharePoint in a folder called “xslfiles.”  You don’t need to have this file also on SharePoint (it could just be a URL too), but in my example, that’s where it’s stored.

I’ve already tested that these two files are interacting fine and producing valid HTML, so all that’s left his to hit OK and publish the page.  Now, whenever I navigate to this page on SharePoint, I get dynamic content imported directly from a third-party via API and transformed into good-looking HTML.

Social Bookmarks

email: zack@locihouse.com
phone: 215.948.2380