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
Comments
Anonymous
July 13, 2011
why would i want to do this?! embed the data in the report.. i must be missing somethingAnonymous
May 30, 2014
Well this is neat and the closest thing to what I need! Unfortunately I don't have any say of my XML structure. It looks pretty much like: <A> <B>1234</B> <C>1</C> <D> <E>1234</E> <E>3465</E> </D> <F> <G> <H>1234</H> <I>ABC1234</I> </G> <G> <H>3456</H> <I>ABC3456</I> </G> </F> <J /> </A> My first hope was to be able to use the .Net libraries to parse the XML. When I had no luck with that I passed the XML field to a subreport and built the dataset like you suggested. I can get the following to show on my sub-report: B, C, E but not H and I. This is all using VS/BIDS 2008. We did build a report with VS 2010 once using only passed XML and I remember having a similar problem: the XML had to be almost flat, couldn't be too many layers deep. In this case I have to use 2008 and it seems that I can only go more than 2 layers deep if I use more subreports! yay. This seems really round-about, is there a better way?Anonymous
September 04, 2014
Muchas Gracias me sirvió demasiado tu ejemploAnonymous
March 13, 2015
The comment has been removed