ssrs report xml

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

XML Connection Type (SSRS)

To include data from an XML data source in your report, you must have a dataset that is based on a report data source of type XML. This built-in data source type is based on the XML data extension. Use this data source type to connect to and retrieve data from XML documents, Web services, or XML that is embedded in the query.

This data extension supports parameters and credentials managed separately from the connection string.

Use the information in this topic to build a data source. For step-by-step instructions, see Add and Verify a Data Connection (Report Builder and SSRS) .

Connection String

The connection string must be a URL that points to the Web service, Web-based application, or XML document available through HTTP. XML documents must have the XML extension. You can also use an empty connection string for XML data embedded in the dataset query.

The following examples illustrate the connection string syntax for a Web service and XML document, respectively. The file:// protocol is not supported.

For more connection string examples, see Data Connections, Data Sources, and Connection Strings in Report Builder .

Credentials

Credentials are required to run queries, to preview the report locally, and to preview the report from the report server.

After you publish your report, you may need to change the credentials for the data source so that when the report runs on the report server, the permissions to retrieve the data are valid.

From a report authoring client, the following options are available to specify credentials:

Current Windows user (also known as integrated security).

No credentials are required. If you select no credentials, Anonymous access is used. Make sure that you have defined the unattended execution account for the report server to connect to an external data source. The XML data processing extension does not pass credentials to the target URL or the Web service; the connection will be unsuccessful unless you have defined the unattended execution account. For more information, see Configure the Unattended Execution Account (Report Server Configuration Manager) .

Stored and prompted credentials are not supported. Remember that if you disable Windows integrated security, you cannot use it to retrieve data. If you specify stored or prompted credentials, an error will occur at run time.

For more information, see Create data connection strings - Report Builder & SSRS or Specify Credential and Connection Information for Report Data Sources .

A query specifies which data to retrieve for a report dataset. The columns in the result set for a query populate the field collection for a dataset. A report processes only the first result set retrieved by a query.

You must use the text-based query designer to create the query. The query must return XML data.

For more information about the text-based query designer, see Text-based Query Designer User Interface (Report Builder) .

The possible values for a dataset query for a data source that is type XML are shown below.

Use an empty query to create a default result set. The default query is created by reading the data source and traversing the XML node hierarchy to the first leaf collection. The result set includes all nodes with text values and all node attributes along that path. Columns in the result set are mapped to fields for the dataset.

An element path

Specifies the sequence of nodes to use when retrieving XML data from the data source.

An XML Query element

An XML query specification with the following optional elements:

XML data source is a Web service

Required XML elements:

<SoapAction> soap action </SoapAction>

Optional XML Elements:

<ElementPath> element path </ElementPath>

XML data source is an XML document

Required XML elements: None

XML data source is an embedded XML document

<XmlData> inner XML </XmlData>

<ElementPath IgnoreNamespaces="true"> element path </ElementPath>

For more information about query syntax, see XML Query Syntax for XML Report Data (SSRS) .

For examples, see Reporting Services: Using XML and Web Service Data Sources .

Requirements for Retrieving XML Web Service Data

The XML data processing extension does not detect the schema for you. Therefore, you must have some way of discovering which SOAP methods will retrieve the data that you want. You must also understand the addressing scheme or namespace that the Web service uses for its data.

For a Web service, you can provide a < Query > element that specifies a method to call or SOAP action. You can leave the query empty and use the default query if the XML data source has a hierarchical structure that produces the data that you want to use for your report. XML element node values and attributes retrieved when the query runs map to the dataset fields you use in your report.

Requirements for Retrieving XML Document Data

Using the http protocol, the server must return XML data or the XML data must be embedded in the XML Query element. If you refer to an XML document directly using the http protocol, the extension must be .xml.

You must know how to create an XML query that retrieves all the data you need. If you do not specify an element path, the default behavior for parsing an XML document is to select the first available path to a leaf-node collection in the XML document. If the XML document includes additional paths to other sibling leaf-node collections, those nodes will be ignored unless you specify a path in your query.

You can provide an element path using XML syntax similar to XQuery.

For more information, see Element Path Syntax for XML Report Data (SSRS) .

The query is not analyzed to identify parameters.

To add parameters, you must create them manually through the Parameter page on the Dataset Properties dialog box.

The XML data extension supports reporting from XML data that is tabular and not hierarchical. For more information, see Add Data from External Data Sources (SSRS) .

There is no built-in support for retrieving XML documents from a SQL Server database.

How-To Topics

This section contains step-by-step instructions for working with data connections, data sources, and datasets.

Add and Verify a Data Connection (Report Builder and SSRS)

Create a Shared Dataset or Embedded Dataset (Report Builder and SSRS)

Add a Filter to a Dataset (Report Builder and SSRS)

Related Sections

These sections of the documentation provide in-depth conceptual information about report data, as well as procedural information about how to define, customize, and use parts of a report that are related to data.

Report Datasets (SSRS) Provides an overview of accessing data for your report.

Create data connection strings - Report Builder & SSRS Provides information about data connections and data sources.

Report Embedded Datasets and Shared Datasets (Report Builder and SSRS) Provides information about embedded and shared datasets.

Dataset Fields Collection (Report Builder and SSRS) Provides information about the dataset field collection generated by the query.

Data Sources Supported by Reporting Services (SSRS) . Provides in-depth information about platform and version support for each data extension.

Report Parameters (Report Builder and Report Designer) Filter, Group, and Sort Data (Report Builder and SSRS) Expressions (Report Builder and SSRS)

Additional resources

SQL Server Reporting Services XML Data Source and Data Set

By: Scott Murray | Updated: 2014-01-03 | Comments (12) | Related: More > XML

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?

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:

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.

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.

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.

Review Element Path Syntax for XML Report Data (SSRS) - http://msdn.microsoft.com/en-us/library/ms365158.aspx

About the author

Comments For This Article

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

How do I display formatted XML in a SSRS Report?

I have a field in my dataset that contains an unformatted XML string, for example:

How do I "prettify" it and display it within a Tablix control? Like this:

reporting-services

This can be achieved by using embedded code in the report and using System.Xml.XmlTextWriter with XmlTextWriterSettings.Indent = true

Open the Report Properties dialog and paste the following function in the Code tab:

You will also need to add a reference to System.Xml , since it is not included by default. Select the 'References' tab in Report Properties, then add System.Xml from the .NET assemblies list:

Then, within the expression of your textbox/table, you can use the following expression:

When you try and deploy the report, you may receive an error like:

This error message is not very useful, but it likely means that your embedded code is incorrect in some way. The most common cause is that you're referencing a class that it can't find. For example, XmlWriterSettings instead of System.Xml.XmlWriterSettings .

Your Answer

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service , privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged xml reporting-services or ask your own question .

Hot Network Questions

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .

Get Report as XML

Applicability:.

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Author: ChillyDBA

Date: 25 Jul 2012

Description

Stored procedure to retrieve report XML using TSQL code. This is stored in the report server database in the Catalog table as varbinary.

From SQL Server 2005 onward, if the query is executed with 'Results to Grid' option, then the XML will be displayed as a clickable link which will open in a new page and display the XML in a formatted code window, otherwise it will be displayed as a single line of XML text.

A filter can be supplied containing a full or partial report name to restrict the results returned

Stored Procedure:

DROP PROC usp_GetReportAsXML

CREATE PROC usp_GetReportAsXML @ReportName NVARCHAR(850) = NULL

/****************************************************

Purpose: To retreive one or many reports in XML format.

Uses the Catalog table in the report server

database (assumed to be called by its default

name of 'ReportServer')

History: 25 Jul 2012

*****************************************************/

c1.Name AS ReportName,

CONVERT(XML,(CONVERT(VARBINARY(MAX),c1.Content))) AS ReportXML,

c2.Name AS ReportFolder

FROM ReportServer.dbo.Catalog c1 (NOLOCK)

INNER JOIN ReportServer.dbo.Catalog c2 (NOLOCK)

ON c1.ParentID = c2.ItemID

WHERE c1.Content IS NOT NULL

AND c1.Name LIKE '%' + ISNULL(@ReportName, c1.Name) + '%' -- if param is null, then return all reports

AND c1.[Type] = 2 -- For Report objects alone

You are using an outdated browser. Please upgrade your browser to improve your experience.

Using XML to Find Errors in a SSRS Report

As we see in the BI779 SQL Server Reporting Services course, a report’s rdl file is actually an XML file. One of the benefits of an XML file is that, since it is text and includes all the objects of our report, we can use it to help us find solutions to certain errors that might otherwise be hard to find. Let’s see an example of this.

Let’s say we have a report with a parameter called @CustID. When we try to run the report, Reporting Services encounters an error in the spelling of a parameter name (@CustoID), but it does not tell us where the error was found.

001-using-XML-to-find-errors-in-a-SQL-Reporting-Services-Report

Parameters can be used in many places, including datasets, textboxes, and others. If you have a large report with several data regions, each containing several textboxes, and/or multiple datasets, it can be a daunting task to find where the misspelling is located. But there is an easy way to pinpoint the location of the error. All we need to do is search for it in the XML file.

1. To open the XML file, right-click on the report .rdl file in Solution Explorer and select View Code .

002-view-code-using-XML-to-find-errors-in-a-SQL-Reporting-Services-Report

2. Notice that the XML file is displayed.

003-xml-displayed-using-XML-to-find-errors-in-a-SQL-Reporting-Services-Report

3. Press Ctrl-F on your keyboard to open the Find and Replace window, enter the text you want to find (@CustoID, in our case) in the Find what field, and then click Find Next .

004-find-replace-custID-using-XML-to-find-errors-in-a-SQL-Reporting-Services-Report

4. In our example, the misspelling is found to be in the SalesOrders dataset.

005-SalesOrders dataset-using-XML-to-find-errors-in-a-SQL-Reporting-Services-Report

5. So, now we know where to go to fix the problem. We right-click the SalesOrders dataset in the Report Data panel and select its properties…

006-SalesOrders-panel-dataset-using-XML-to-find-errors-in-a-SQL-Reporting-Services-Report

6. …and we can now easily locate the problem and fix it.

007-fixed-dataset-using-XML-to-find-errors-in-a-SQL-Reporting-Services-Report

And that’s it!

Enjoy! Peter Avila SQL Server Instructor – Interface Technical Training Phoenix, AZ

Videos You May Like

Cable Testers and How to Use them in Network Environments

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

How to use the PowerShell Script Analyzer to Clean Up Your Code

OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj

In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.

Write a Comment

Share your thoughts....

Please fill out the comment form below to post a reply.

Save my name, email, and website in this browser for the next time I comment.

Insert/edit link

Enter the destination URL

Or link to existing content

How do I use PBRS to export Paginated Reports or SSRS reports to XML format?

Export paginated reports and ssrs reports to xml & automatically deliver them to an ftp site, send them in emails, or deliver them to any number of other destinations..

This article applies to:

XML (*.xml) outputs for Paginated Reports or SSRS

To export XML outputs in Paginated Reports or SSRS, follow these steps:

Power BI and SSRS. XML output format section in Destination Wizard in PBRS

The output format is selected as part of the Destinations setup for each destination type. The tab above can be found in the Destinations section of a schedule.

Format Option

File Summary

These options determine how the exported file will be named:

XML (*.xml) outputs for Power BI Service Reports & Dashboards

XML as an export format is currently not available for Power BI Service Reports & Dashboards.

Instantly share code, notes, and snippets.

nibblesnbits / ExtractReportServerXML.sql

IMAGES

SSRS Report Builder introduction and tutorial
SQL Server Reporting Services (SSRS)
SSRS Reporting Basics: When is SSRS the Right Tool?
SSRS Report Builder introduction and tutorial
MicrosoftDynamicsAxapta: SSRS Report
SSRS Tutorial: What is SQL Server Reporting Services?

VIDEO

"Now I'm Going to Kill You!" Police Report on Chris Brown Alleged Attack on Rihanna
The Elite Punch Their Ticket to All Out For the AEW Trios Tournament Finals
SSRS Demo Part 1
SSRS
TestNG Report, XML File, and Priority
SSRS: How to Deploy single or multiple reports to reporting server

COMMENTS

XML Query Syntax for XML Report Data (SSRS)
In Reporting Services, you can create datasets for XML data sources. After you define a data source, you create a query for the dataset.
XML Connection Type
The XML data extension supports reporting from XML data that is tabular and not hierarchical. For more information, see Add Data from External
SQL Server Reporting Services XML Data Source and Data Set
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
How to resolve SSRS report design open in XML format ...
This video will solve the problem of the visual studio while opening the SSRS report it opens in XML format instead report view.
How to view the XML code related to reports
Right-click on the Report 03-03 Nested Data Regions file. · In the menu, select View Code. · You will now see the XML behind the report. At over 650 lines, the
How do I display formatted XML in a SSRS Report?
This can be achieved by using embedded code in the report and using System.Xml.XmlTextWriter with XmlTextWriterSettings.Indent = true.
Reports and SSRS
Description. Stored procedure to retrieve report XML using TSQL code. This is stored in the report server database in the Catalog table as varbinary. From
Using XML to Find Errors in a SSRS Report
As we see in the BI779 SQL Server Reporting Services course, a report's rdl file is actually an XML file. One of the benefits of an XML file
Element Path Syntax for XML Report Data (SSRS)
Element Path Syntax for XML Report Data (SSRS). In Report Designer, you specify the data to use for a report from an XML data source by defining a
How do I use PBRS to export Paginated Reports or SSRS reports to
Power BI Paginated Reports (On Premise - Power BI Report Server); Microsoft SSRS Reports (On Premise - SQL Server Reporting Services). XML (*.xml) outputs for
Extracts the CommandText and XML from SSRS ReportServer
extracted from http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/. DECLARE @ItemID UNIQUEIDENTIFIER