Problem
Is it possible to use XML data / files as a source for SQL Server Reporting Services (SSRS) reports? Are there any limitations to using an XML source?
Solution
XML data sets and files can be used as a source for SSRS reports, but there are several limitations and rules which must be followed in order to use an XML source. First, in order to use the XML data it must reside in one of three locations:
- locally embedded in the report definition file ( the SSRS rdl ),
- an XML document which is linked to via a URL HTTP connection
- a URL which points to a web service which in turn returns XML data. You can use a web service or a soap action.
Notice that an XML data type field retrieved from a SQL Server table is not listed as one of the available sources of XML data. You would want to use a specific query method such as XQuery to retrieve data from an XML column. Seth Delconte wrote an excellent tip on using XQuery which is available at https://www.mssqltips.com/sqlservertip/2889/basic-sql-server-xml-querying/. Also, note that the second option allows access only via the http protocol and not via the file for ftp protocol.
XML Data Sources and Datasets
The first step to retrieve XML data via SSRS is to define the data source. For an XML data source, no wizard is available, so you must manually complete the connection details for the connection string. This tip will cover the details of the three data source methods that can be used.
The first type is a connection to XML which is embedded in the report itself. This type of data source is actually the easiest to create since the data source connection string contains nothing other than the name, it is blank as shown below.
Switching to the Credentials window, you will need to set the proper permission; in the below case, we are using Windows Authentication.
Now we are ready to create a dataset based on this data source. For the local method, we need to pull in the data from XML embedded within the report / data set itself. As noted below, a new shared data set is created using the previously created local data source. Notice how within the Query section the XML detail is listed out completely.
The XML query is broken out at the highest element of the XML structure; drilling into deeper levels of the XML schema could be achieved by adjusting the query added into the query designer.
From this point, a matrix, a table, or a tablix could be created with some of the data returned from the local XML dataset. Such an example report is included below.
Moving on from the local XML data source, we will next explore using an HTTP link to an XML document. This method requires building a data source linked to, in essence, a website hosting a XML document; again no wizard is available. However the syntax is quite straight forward; as shown in the succeeding screen print, you just define the website and file name where the XML file resides. In the below example, I am using the W3Schools site (http://www.w3schools.com/ ) for the source of our XML (as a side note, this site provides plenty of examples for both XML and many other web development file types such as html, java script, and asp.net to name a few).
Again, the Credentials must be adjusted; for this example, we use Windows Authentication, as displayed below.
Next we need to define the data set and specifically the query which will be run against the URL based XML file.
The query has several parts. First, we have the <Query> tags and then the <ElementPath> tags. The <ElementPath> tags defines the schema level and elements items to be returned by the query. In the above example, we drill down to the Catalog/CD level within the XML file and specifically return the Artist, Year, and Title fields for each CD. The above query returns the below results (partial list).
Of course, once you have the data set created, it can be used as the source for a report server table, matrix, or tablix as illustrated below.
The URL linked XML file method could be used for many purposes; for instance, you have a daily feed for currency rates or commodity prices which you want to apply to your reports.
The last XML data source available for SSRS reports is using a URL which points to a web service. A common use for this type of data source pertains to actually interrogating the SSRS web service to get information about the reports and properties on the SSRS web service. To see other, currently available, web services for SSRS, you can review: http://technet.microsoft.com/en-us/library/ms155398.aspx. Again no wizard exists to create the data source connection string; however the connection string just points to the location of the web service or soap action location. Similarly, you need to set the credentials as we have for previous data source.
In order to query all data at the top of the web service, simply writing <Query></Query> will return all top level data for the web service.
This basic query returns the following results.
We could further define the query by adding and defining some methods, namespaces, parameters, and elements as shown below. This query specifically requests details about the reports and their folder structures.
The object name, object type, folder path, create date, and modification date are all returned from this query as illustrated below.
Again, as displayed below, these results could easily be included in a report.
Conclusion
SSRS allows report designers to use XML as data source for data set reports. XML data sources are restricted to three types of data sets: 1) XML embedded within the report itself, 2) XML file linked via a HTTP URL, or 3) a web service linked via a HTTP URL. The data source setup does not include a wizard for designing the connection string, so it must be manually defined; however defining the connection string is as simple as leaving the connection string blank for an embedded data source or adding the complete URL for a web service or linked file data source. After defining the data source, the data set query must be created. The query is expressed by using the <Query> </Query> tags. A top level query can include just the <Query> </Query> tags which will include all elements at the top level of the XML schema, or a query can include specific XML schema level and element definitions. Once the query is defined in the dataset, a report can be created based on this dataset.
Next Steps
- Review Element Path Syntax for XML Report Data (SSRS) – http://msdn.microsoft.com/en-us/library/ms365158.aspx
