Introduction

Starting from version 3.4, it is possible to query SQL Server databases directly. The resulting data can be join with SharePoint data using a “SqlQuery” statement the same way you can join multiple SharePoint lists.

On several occasions, we have been asked if it was possible to use Enesys RS Data Extension for joining SQL data and SharePoint data in the same report. Until version 3.1, this was simply impossible. Though we will be adding a more direct support for querying over SQL tables in a future version, ERSDE 3.1 makes it possible to join both SQL and SharePoint data through the use of the “xmlQuery” statement.

The “xmlQuery” statement lets you query an URL you specify for retrieving data in an XML format. It is therefore, possible to write some .aspx (or any other technology) page that will query a SQL database and return the result in XML format. The data can then be consumed by an “xmlQuery” statement and joined with SharePoint data.

This is exactly what we will demonstrate in this sample.

Here is the scenario:

We have a SharePoint list that contains Products and we would like to build a report displaying the products grouped by category. In our report we would like to display the description of each category as well as their name.

Details about categories reside in a SQL table. We will therefore need to join both SharePoint Product’s list SQL Category’s table.

No need to say that we are using data from the famous Northwind database.

What we need to do:

1. Creating an ASPX page that will query the Categories SQL table and return XML data.
2. Publishing and testing the page.
3. Retrieving data from the page we have published using the xmlQuery statement.
4. Joining the data with the SharePoint Product list.

Creating an aspx page to query SQL data and return XML data

Our aspx page is quite simple. It will :

• query the Northwind’s Categories table,
• fill a dataset,
• use dataset WriteXml method to convert the resulting data into XML,
• write this XML data to the response stream.

Here is our SqlData.aspx page:

      `            <%@ Page="" Language="C#" AutoEventWireup="true" %=""><%@ Import="" Namespace="System.IO" %=""><%@ Import="" Namespace="System.Data.SqlClient" %=""><%@ Import="" Namespace="System.Data" %=""></%@></%@></%@></%@>`