SQL Server Reporting Services XML Data Source and Data Set

By:   |   Comments (13)   |   Related: > XML


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: 

  1. locally embedded in the report definition file ( the SSRS rdl ),
  2. an XML document which is linked to via a URL HTTP connection 
  3. 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 http://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.  

Local Data Source

Switching to the Credentials window, you will need to set the proper permission; in the below case, we are using Windows Authentication.   

Local Credentials

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.

Local Data Set

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. 

dataset example

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.

local report

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). 

URL Data Spirce

Again, the Credentials must be adjusted; for this example, we use Windows Authentication, as displayed below.

URL Data Source Credentials

Next we need to define the data set and specifically the query which will be run against the URL based XML file.

URL Data Set

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).

URL Query Results

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. 

URL Report Preview

URL Report Preview

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.  

Web Service 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.

Web Service All Data

This basic query returns the following results.

Web Service All Data

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.

Web Service Partial Query

The object name, object type, folder path, create date, and modification date are all returned from this query as illustrated below.

Query Results

Again, as displayed below, these results could easily be included in a report.

SSRS Web Service

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, February 22, 2024 - 2:26:18 AM - Iqra Technology Back To Top (92001)
SSRS provides flexibility in utilizing XML as a data source for report datasets. While the setup might require manual configuration of the connection string, the process is straightforward. Defining the query within the <Query> </Query> tags allows for precise control over the data retrieved from the XML schema. This feature enables report designers to create comprehensive reports tailored to their specific needs.

Thursday, August 12, 2021 - 6:47:58 AM - Mark Goldin Back To Top (89118)
I have a SOAP request that I need to convert into XML Query. The SOAP works in Postman where I use form/raw for the payload. But in the report I am not sure how to do it. I am getting an error that one of the parameters weren't provided. Here is my XML Query:

<Query>
<Method Name="LoginOTP" Namespace="http://schemas.xmlsoap.org/soap/envelope/">;
<Parameters>
<Parameter Name="ClientID">
<DefaultValue>xxxxxxx</DefaultValue>
</Parameter>
<Parameter Name="GUID">
<DefaultValue>some_guid</DefaultValue>
</Parameter>
<Parameter Name="User">
<DefaultValue>xxx</DefaultValue>
</Parameter>
<Parameter Name="Password">
<DefaultValue>xxxx</DefaultValue>
</Parameter>
</Parameters>
</Method>
<SoapAction>abc/hhh/lkklk</SoapAction>
<Host>abc.sddd.dd.dd</Host>
</Query>

Wednesday, February 3, 2016 - 12:55:27 PM - scott murray Back To Top (40582)

 I have not tried dynamic XML. I would suspect it would work.

 

Wednesday, February 3, 2016 - 7:42:50 AM - Arun Back To Top (40580)

 

Hi 

How we can use dynamic xml file source in ssrs.

Tuesday, June 23, 2015 - 4:52:04 PM - Bret Back To Top (38002)

SCOTT! We found it.  This is how you would do a REST implementation

 

Report Parameters are defined: CompanyName as String, InvoiceDates as String

The Datasource is an expression we put this in:

="http://domain.com/rest/"& Parameters!CompanyName.Value & "/" & Parameters!InvoiceDates.Value

 

 The dataset is just empty

<Query></Query>

Resulting URL: http://domain.com/rest/Magnavox/201403

 

Bret

Tuesday, June 23, 2015 - 2:22:08 PM - Bret Back To Top (37997)

Scott- So defining the...will get it to the servlet?  

I've done all the other mappings in SSRS, but I can't see how these values would make it to the Http Post on the webserver side.  Will they show up in the HTTP POST data that gets sent across?

 

 

 

Thanks,

Bret

Tuesday, June 23, 2015 - 9:47:42 AM - scott Back To Top (37992)

Maybe I mis understood what you are wanting to do.... You should be able to modify the query to add a parameter..

 

something like.
   <Parameters>
       <Parameter Name="Item">
           <DefaultValue>test</DefaultValue>
       </Parameter>
   </Parameters>

Tuesday, June 23, 2015 - 8:56:54 AM - Bret Back To Top (37990)

Scott- That is absolutely incredible, unbelievable.  The simplest form of http communication and the world's biggest dev company doesnt provide for it in their flagship reporting tool.

 

How about getting parameters into a restful interface? Like filling in this URL.

http://domain.com/rest/?CompanyName?/?InvoiceDates?

Do you know a way of doing that? 

 

Thanks,

Bret

Friday, June 19, 2015 - 10:20:11 PM - scott Back To Top (37968)

Bret:  Unless something has changed recently, Unfortunately not .

Friday, June 19, 2015 - 4:29:43 PM - Bret Back To Top (37967)

Scott, thanks for the tutorials! These are very thorough.  I couldn't find one answer, however... How do you pass parameters to an HTTP/URL based XML datasource?  The above shows embedded XML, Static URL-based XML, and WebService, but we want to pass the params over the wire to our servlet - such as a start and stop date.  Is this even possible?

 

Thanks.

Bret

Tuesday, June 16, 2015 - 10:21:47 AM - Scott Murray Back To Top (37935)

Not that I aware of.

Tuesday, June 16, 2015 - 6:15:42 AM - Kumar Back To Top (37933)

Thanks for you post.

I'm using the XML datasource to develop a report using a webservice. In text i given. If there is no record in the dataset. I'm getting error message in report.

Is there any possible to find the empty dataset.

Thursday, January 15, 2015 - 11:44:00 AM - Miguel A. Chavez Garcia Back To Top (35959)

Thank you. Good sample. 














get free sql tips
agree to terms