XSL Transformation with SPSiteDataQuery


This post demonstrates how to take the data returned from an SPSiteDataQuery query, convert it to XML and then to transform the XML using XSLT for presentation.

The features described in this post include;

  • A basic XSLT transformation stylesheet
  • Loading an XSLT transformation stylesheet from the content database
  • Supplying parameters to the XSLT transformation engine
  • Creating and supplying custom extension functions to the XSLT transformation engine

Basic XSLT Transformation Stylesheet.
The XSLT transformation that will be used is shown below;

<xsl:stylesheet version="1.0"
					 exclude-result-prefixes="x d xsl msxsl cmswrt"
					 xmlns:x="http://www.w3.org/2001/XMLSchema"
					 xmlns:d="http://schemas.microsoft.com/sharepoint/dsp"
					 xmlns:cmswrt="http://schemas.microsoft.com/WebParts/v3/Publishing/runtime"
					 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
					 xmlns:msxsl="urn:schemas-microsoft-com:xslt"
					 xmlns:xslUtils="urn:xslUtils">

	<xsl:output omit-xml-declaration="yes"/>
	<xsl:param name="QS-SiteId" />
	<xsl:param name="QS-SiteUrl" />
	<xsl:param name="QS-HostUri" />
	<xsl:param name="QS-Source" />
	<xsl:param name="QS-Debug" />

	<xsl:template match="/">
		<xsl:if test="$QS-Debug != '0'">
			QS-SiteId: <xsl:value-of select="$QS-SiteId"/>
			<br/>
			QS-SiteUrl: <xsl:value-of select="$QS-SiteUrl"/>
			<br/>
			QS-SiteUrl (Unencoded): <xsl:value-of select="xslUtils:UrlDecode($QS-SiteUrl)"/>
			<br/>
			QS-HostUri: <xsl:value-of select="$QS-HostUri"/>
			<br/>
			QS-Source: <xsl:value-of select="$QS-Source"/>
			<br/>
			QS-Debug: <xsl:value-of select="$QS-Debug"/>
		</xsl:if>
		<xsl:apply-templates select="rows/row" />
	</xsl:template>

	<xsl:template match="row">
		<div id="linkitem" class="item link-item bullet" style="background-repeat:no-repeat;background-image:url(/_layouts/images/bullet.gif);background-position:left 4px;padding-left:10px;">
			<a href="{concat('/_layouts/CopyUtil.aspx?Use=id&amp;Action=dispform&amp;ItemId=',ID,'&amp;ListId=',ListId,'&amp;WebId=',WebId,'&amp;SiteId=',$QS-SiteId,'&amp;Source=',$QS-Source)}">
				<xsl:value-of select="Title"/>
			</a>
		</div>
	</xsl:template>
</xsl:stylesheet>

Convert DataTable to XML.
Assuming that you’ve executed an  SPSiteDataQuery query, you will have a DataTable object containing the resultant data. The first task therefore is to convert this DataTable to some appropriate XML, the code below does this loosely following the same schema as the Content Query Web Part.

// prepare the supplied DataTable
results.TableName = "row";
// prepare a new DataSet
var ds = new DataSet("rows");
ds.Tables.Add(results);

// retrieve the XML
var xml = ds.GetXml();

In this case, the columns (fields) will be child elements of the row element, rather than attributes (as with the CQWP).

Load the XSLT Transformation Stylesheet.
We will load the stylesheet from the site collection /Style Library/XSL Style Sheets document library as shown and create an XslCompiledTransform object;

/// <summary>
/// Gets the text file as bytes.
/// </summary>
/// <param name="url">The URL.</param>
/// <returns></returns>
private static byte[] GetTextFileAsBytes(string url)
{
	using (var site = new SPSite(url))
	{
		var web = site.OpenWeb();
		var file = web.GetFile(url);

		// open binary file contents
		var bytes = file.OpenBinary();
		return bytes;
	}
}

/// <summary>
/// Loads the transformation (characterset/encoding safe).
/// </summary>
/// <returns></returns>
private XslCompiledTransform LoadTransformation()
{
	var xslFilePath = GetXslFileUrl();

	// load the XLST file
	var xslTransform = new XslCompiledTransform();

	var xslBytes = GetTextFileAsBytes(xslFilePath);
	using (var stream = new MemoryStream())
	{
		stream.Write(xslBytes, 0, xslBytes.Length);
		stream.Flush();
		stream.Seek(0, SeekOrigin.Begin);

		using (var reader = new XmlTextReader(stream))
		{
			// load the transformation
			xslTransform.Load(reader);
		}
	}

	return xslTransform;
}

This technique of loading the stylesheet circumvents any characterset or file encoding/BOM issues as described in this post.

Create and Supply Parameters to the XSLT Transformation Engine.
To do this we need to create an instance of the XsltArgumentList class, add parameters to it and pass it to the Transform method of the XslCompiledTransform class;

var args = new XsltArgumentList();
var siteUrl = "";
var hostUri = "";
var sourceUrl = "";

args.AddParam("QS-SiteId", "", SPContext.Current.Site.ID.ToString());
args.AddParam("QS-SiteUrl", "", HttpUtility.UrlEncode(siteUrl ?? string.Empty));
args.AddParam("QS-HostUri", "", HttpUtility.UrlEncode(hostUri));
args.AddParam("QS-Source", "", HttpUtility.UrlEncode(sourceUrl ?? string.Empty));
args.AddParam("QS-Debug", "", "0");

Creating and Supplying Custom Extension Functions to the XSLT Transformation Engine.
First create a class which implements the extension functions you want to provide to the XSLT engine, like so;

internal class XslUtils
{
	public string UrlDecode(string url)
	{
		if (string.IsNullOrEmpty(url))
			return string.Empty;
		var res = HttpUtility.UrlDecode(url);
		return res;
	}
}

Create an instance of it and pass it to the AddExtensionObject method of the XslCompiledTransform class along with an appropriate namespace URI;

var xslUtils = new XslUtils();
args.AddExtensionObject("urn:xslUtils", xslUtils);

To use the extension function in your XSL, you must declare the namespace URI in the stylesheet element as shown (last attribute/line of the stylesheet element);

<xsl:stylesheet version="1.0"
					 exclude-result-prefixes="x d xsl msxsl cmswrt"
					 xmlns:x="http://www.w3.org/2001/XMLSchema"
					 xmlns:d="http://schemas.microsoft.com/sharepoint/dsp"
					 xmlns:cmswrt="http://schemas.microsoft.com/WebParts/v3/Publishing/runtime"
					 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
					 xmlns:msxsl="urn:schemas-microsoft-com:xslt"
					 xmlns:xslUtils="urn:xslUtils">

To use the custom extension function in your stylesheet you simpy refer to the extension function (your class method) via it’s declared namespace prefix like this;

<xsl:value-of select="xslUtils:UrlDecode($QS-SiteUrl)"/>

Finally, you perform the transformation by supplying the transformation engine with the XML data retrieved from your SPSiteDataQuery query;

using (var xtr = new XmlTextReader(new StringReader(xml)))
{
	oTransform.Transform(xtr, args, writer);
}

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s