Walkthrough: How to change XML data for embedded XML document in a SQL Server Reporting Services (SSRS) report
In Microsoft SQL Server Reporting Services (SSRS), you can use XML data source type to connect to and retrieve data from XML documents, Web services, or embedded XML snippets in queries. For embedded XML snippets, people often ask how to change the XML attributes or elements values dynamically under a specified XML schema. This article will show you the steps of the resolution.
Note: This walkthrough is only available in SSRS 2008 or 2008 R2. In SSRS 2005, you may encounter the error :
An error occurred while setting the Command Text property of the data extension command.
The XmlDP query is invalid. Syntax error at line <line number>, character <number> of the ElementPath
However you can ignore the error, and continue to design and preview the report.
Create a Shared Data Source or embedded Data Source that is using XML data source type.
Shared Data Source:
1. In the Business Intelligence Development Studio (BIDS), in the Solution Explorer, right-click Shared Data Sources.
2. Then click Add New Data Source.
3. In the Shared Data Source Properties dialog, type a name for the data source, select type XML, and leave the connection string blank.
Embedded Data Source:
1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Data Sources.
2. Then click Add Data Source.
3. In the Shared Data Source Properties dialog, type a name for the data source, select type XML, and leave the connection string blank.
4. Go to Credentials tab, check Use Windows Authentication(Integrated Security)
Create a report parameter in the report.
1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Parameters.
2. Then click Add Parameter… .
3. In the Report Parameter Properties dialog, type a name and a prompt for the parameter(e.g. XMLData)
4. Go to Default Values
5. Check Specify Values, and then click Add to add a value. The value is the XML document.
Change a dataset in the report.
1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Datasets.
2. Then click Add Dataset… .
3. In the Dataset Properties dialog, in the Query tab, type a name for the dataset
4. Choose the data source created before for this dataset.
5. In the Query textbox, type in the following query:
="<Query><XmlData>" &Parameters!XMLData.Value &"</XmlData></Query>"
6. Go to Fields tab
7. Add Query Field for this dataset. Please note, you need to know the element path before using it in the step. You can get all the fields first using full embedded query directly.
Now, you can change the XML data from the parameter to change the XML data for the report.
Reference
XML Connection Type (SSRS)
https://msdn.microsoft.com/en-us/library/dd220468.aspx