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)
- 6 minutes to read
- 11 contributors
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) .
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 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:
<Method Namespace= "namespace" Name="MethodName" />
<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.
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)
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)
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:
- 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 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
- Stack Overflow Public questions & answers
- Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
- Talent Build your employer brand
- Advertising Reach developers & technologists worldwide
- About the company
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:
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 .
- This works great on my local host but it does not work when deployed to a remote server. I'm not getting an error but the XML is not formatted. I've found a lot of information about deploying custom assemblies in SSRS but nothing about troubleshooting the use of .NET assemblies. Any ideas how I can troubleshoot this? – John81 Dec 31, 2014 at 14:36
- I notice that IE doesn't read the indentations at different depths, while Chrome and Mozilla do ( screencap ). I haven't debugged yet, but I'm wondering if you encountered this / have any tips? +1 for this in either case!! – Peter Vandivier Mar 18, 2016 at 19:23
- 1 side note : the XML has to be in a TXT BOX element.. and not inside a tablix.. this doesn't work inside a tablix which had me scratching my head for a while! – Harry May 17, 2021 at 2:31
Sign up or log in, post as a guest.
Required, but never shown
Not the answer you're looking for? Browse other questions tagged xml reporting-services or ask your own question .
- The Overflow Blog
- Can Stack Overflow save the day?
- Let’s talk large language models (Ep. 546)
- Featured on Meta
- We've added a "Necessary cookies only" option to the cookie consent popup
- The Stack Exchange reputation system: What's working? What's not?
- Launching the CI/CD and R Collectives and community editing features for...
- Staging Ground Beta 1 Recap, and Reviewers needed for Beta 2
- Temporary policy: ChatGPT is banned
Hot Network Questions
- What kind of screw has a wide flange with a smaller head above?
- What is the cause of the constancy of the speed of light in vacuum?
- Where can I create nice looking graphics for a paper?
- Is it safe to have an unused bulb socket in my ceiling fan?
- What type of regression to use when outcome is integers from 0 to 5
- Why didn't SVB ask for a loan from the Fed as the lender of last resort?
- Next word in this progression
- How are the banks behind high yield savings accounts able to pay such high rates?
- How do unpopular policies arise in democracies?
- Why does cat with no argument read from standard input?
- How to design a schematic and PCB for an ADC using separated grounds
- What are the black pads stuck to the underside of a sink?
- Is there such a thing as "too much detail" in worldbuilding?
- Explain Like I'm 5 How Oath Spells Work (D&D 5e)
- Unmatched records missing from spatial left join
- What is the reason the Kronecker delta function is noted this way in DSP?
- Linearize conditional constraint
- What does a client mean when they request 300 ppi pictures?
- Change the background color of the active windows
- How much technical / debugging help should I expect my advisor to provide?
- Is "inauthentic" inauthentic?
- What is dependency grammar and what are the possible relationships?
- How can I check if this airline ticket is genuine?
- Can I interpret `< file1 cmd1` as `cmd1 file1`?
Get Report as XML
SQL Server 2000: Not Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 25 Jul 2012
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
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.
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 .
2. Notice that the XML file is displayed.
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 .
4. In our example, the misspelling is found to be in the SalesOrders dataset.
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…
6. …and we can now easily locate the problem and fix it.
And that’s it!
Enjoy! Peter Avila SQL Server Instructor – Interface Technical Training Phoenix, AZ
You May Also Like
Using command line utilities for troubleshooting name…, how to use file hashing and integrity monitoring algorithms…, understanding network cable testers, securing your small office systems.
Report Data , Reporting Services , Solution Explorer , SQL Reporting Services , SSRS , XML
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
In this video, PowerShell instructor Jason Yoder demonstrates how you can use the PowerShell Script Analyzer to help you format your code to best-practices. For instructor-led PowerShell training classes, see our course schedule: Microsoft Windows PowerShell Training Download the PowerShell Analyzer scripts used in this video. <# ╔══════════════════════════════════════════════════════════════════════════════╗ ║ ║ ║ PowerShell Script Analyzer ║ … Continue reading 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.
Enter the destination URL
Or link to existing content
- Getting Started with PBRS
- PBRS Technical FAQs
- Getting Started with CRD
- CRD Technical FAQs
- Getting Started with IntelliFront BI
- Using IntelliFront BI
- Help Center & Knowledge Base
- PBRS for Power BI and SSRS
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:
- Power BI Service Paginated Reports (Cloud - Power BI Service)
- Power BI Paginated Reports (On Premise - Power BI Report Server)
- Microsoft SSRS Reports (On Premise - SQL Server Reporting Services)
XML (*.xml) outputs for Paginated Reports or SSRS
To export XML outputs in Paginated Reports or SSRS, follow these steps:
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.
These options determine how the exported file will be named:
- This is the option where you name the output file.
- Default Naming Convention: PBRS will name the output file in the following format: reportname.format extension, e.g. Catalog Report.pdf.
- Customize the output file name: Choose your own filename or right-click and use the Insert Function to insert a value.
- Customize output extension: Choose your own extension. This is useful for system integration. For example, the default extension for a character separated file is "CSV," but you can give your export an extension of "txt" so that the file can be read by another already existing system you may have. You may also right-click and use the Insert Function to insert a value.
- Append date/time: This is useful for the following reasons:
- If the filename is the same each time, and it is being exported to the same folder each time, then it will be overwritten by the latest one each time. By appending date and time to the filename, each file remains unique, and no files are overwritten.
- You can track which reports ran and when they ran by looking at what the report is named.
- Compress (ZIP) Output: Zips the output. Ability to use .zip encryption as well.
- Zip File Encryption: Check the option to encrypt and password protect the zip file.
- Defer Delivery: The report will be generated at the scheduled time, but will not be delivered to the specified destination until later.
- For more information, go to Deferred Delivery
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.
In Reporting Services, you can create datasets for XML data sources. After you define a data source, you create a query for the dataset.
The XML data extension supports reporting from XML data that is tabular and not hierarchical. For more information, see Add Data from External
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
This video will solve the problem of the visual studio while opening the SSRS report it opens in XML format instead report view.
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
This can be achieved by using embedded code in the report and using System.Xml.XmlTextWriter with XmlTextWriterSettings.Indent = true.
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
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). In Report Designer, you specify the data to use for a report from an XML data source by defining a
Power BI Paginated Reports (On Premise - Power BI Report Server); Microsoft SSRS Reports (On Premise - SQL Server Reporting Services). XML (*.xml) outputs for
extracted from http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/. DECLARE @ItemID UNIQUEIDENTIFIER