Share via

Create Reports from XML Data Source and XML Source from Sql Server Table

  • Article

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

http://jeeveshfuloria08.files.wordpress.com/2011/05/1-sql.png

 select * from empsal for xml path('no'),root('ename')

http://jeeveshfuloria08.files.wordpress.com/2011/05/2-sql.png

Click on that XML it will open in other query window. Now you can see the XML format of selected table.

http://jeeveshfuloria08.files.wordpress.com/2011/05/3-sql.png

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.

http://jeeveshfuloria08.files.wordpress.com/2011/05/1-r.png

Then we have to select another tab Credentials and here we have to select "use windows authentication (integrated Security) and click Ok.

http://jeeveshfuloria08.files.wordpress.com/2011/05/2-r.png

Now Data Source created , we have to create dataset so right click on DataSource which you created earlier and select Add DataSet.

http://jeeveshfuloria08.files.wordpress.com/2011/05/4-r.png

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>

http://jeeveshfuloria08.files.wordpress.com/2011/05/3-r.png

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.

http://jeeveshfuloria08.files.wordpress.com/2011/05/5-r.png

If you click on the preview tab then you can see the data and charts.

http://jeeveshfuloria08.files.wordpress.com/2011/05/6-r.png

So that's all about the procedure how we have to create reports from XML data source.

See Also

  • [[SQL Server Reporting Services Portal]]