Create reports from xml data source and xml source from Sql Server table.

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

and write the command for generating the xml file in sql server.In result window SSMS shows the query output in XML format.

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.

Advertisements

About jeeveshfuloria

i am working in iSpace global solution pvt. ltd. in MSBI field and pursuing b.tech through AMIE.
This entry was posted in SSRS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s