Generating an SSRS Report From an XML Datasource

We can retrieve data from XML data sources directly with Reporting Services using the XML data provider. It converts XML structure into a data set and SSRS uses same to generate report.

We can use the XML content directly within the query and can generate report. We can build query using expressions to build queries and data dynamically within the report. We can use XML data provider in any of below manners.

XML Embedded Within the Query

We can use the XML content directly within the query and can generate report. We can build query using expressions to build queries and data dynamically within the report.

XML Using URL

We can use HTTP protocol to read XML content and can generate data based on that. In this post, we have sample with this option.

XML Using Web Services

The XML data provider can request Web services and in response of that, it can parse SOAP response into XML structure. We will see example of this option soon in some next post.

Let’s review one example of XML data provider using URL here. 

Step 1
As I have already mentioned that we can use any external xml file in HTTP protocol and can generate data, I have make one virtual directory in my localhost for demo purpose and create one xml file named “MyXMLFile2.xml” which gives data in XML structure (figure 1).

Figure 1

01.<Invoices>

02.<Invoice>

03.<InvoiceDetail>

04.<InvoiceNo>12201</InvoiceNo>

05.<Name>Sarah Greenberg</Name>

06.<InvoiceDate>03/15/2010</InvoiceDate>

07.<InvoiceAmount>145</InvoiceAmount>

08.</InvoiceDetail>

09.<InvoiceDetail>

10.<InvoiceNo>12203</InvoiceNo>

11.<Name>Joe Gonzalez</Name>

12.<InvoiceDate>04/12/2010</InvoiceDate>

13.<InvoiceAmount>255</InvoiceAmount>

14.</InvoiceDetail>

15.</Invoice>

16.</Invoices>

Step 2

Create new datasourace with “XML” as datasourace type. In connection string, you need to enter virtual path of your xml file like in this example, I have entered here 'http://localhost/MyXMLFile/MyXMLFile2.xml' in this new datasource “Datasource1” in Figure 2.

Figure 2

Step 3

Create new dataset like “DatasetXML” in Figure 3. Assign “Datasource1” as datasource , We can write query here to retrieve data from our source xml file mentioned in Figure 1. Keep Query type as “text” as its xml datasource. “Query” retrieves data here based on XPath in “Element Path”. You can keep query area blank and make query using “Query Designer” too. Click on button “Query Designer” for same.

 

Figure 3

Step 4

I like to make query from dialogue box as I can write my query here with different “element path” and preview data in same box by selecting the exclamation button (!) (Figure 4). The “element path” states a structure using XPath, and by the XML Data Provider retrieves the data from the XML datasource as per this element path. Query uses XPath but pay attention here in syntax. I have added empy {} here as I do not want values from these nodes.

You can copy code for “query” from here. 

1.<Query>

2.<ElementPath>

3.Invoices {}/es:Invoice{}/es:InvoiceDetail

4.</ElementPath>

5.</Query>

Figure 4

As you save your dataset changes , it would create datasource and you can find fields of query under new datasource in Data Window (Figure 5). Now you can design table and assign different fields in table in RDL file and your report is ready for preview (Figure 6)

Figure 5

Figure 6

So report is ready now. I hope, you like it !!!