Create reports from xml data source and xml source from Sql Server table.
Summary: This is an example of creating reports from XML data source in SSRS.
Business Scenario: In some situation we have to create reports in SSRS but data source should be in XML format & some times we have to generate XML from Sql Server tables.
Solution: Firstly we have to create report in SSRS and we can see the table which we want to convert into xml, so just open the new query window.
select * from empsal
select * from empsal for xml path('no'),root('ename')
Click on that XML it will open in other query window. Now you can see the XML format of selected table.
Now we have to go to Report in BIDS and select the Report Data, click on new then select Data Source... and in data source we have to put the name of Data Source and in Embedded connection we have to select XML type.
Then we have to select another tab Credentials and here we have to select "use windows authentication (integrated Security) and click Ok.
Now Data Source created , we have to create dataset so right click on DataSource which you created earlier and select Add DataSet.
Then go to query designer. here you have to copy the xml file which you already generated in T-SQL and you have to add few things along with XML query and then click on run command it will show the output then click Ok.
<Query>
<XmlData>
<Root>
-------Here you have to paste the xml format created by T-sql.---------
</Root>
</XmlData>
</Query>
Data set is ready and we have to create reports. We can create any type of reports such as tabular, matrix, bar charts etc. so here i am creating different type of report in one page.
If you click on the preview tab then you can see the data and charts.
So that's all about the procedure how we have to create reports from XML data source.