Lesson 3: Defining a Report Dataset from Embedded XML Data

Collapse All

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

Use the following steps to learn how to retrieve report data from XML data embedded in the dataset query for a Reporting Services XML data source. In this example, XML data for a SQL query result set from the AdventureWorks sample database has been embedded in a dataset query. This tutorial also shows how to specify the element nodes and attributes you want to retrieve for the report dataset in the ElementPath tag of the query.

Procedures

To create the query for the dataset

Open Microsoft SQL Server Management Studio.
On the Connect to Server dialog box, choose Server type Database Engine.
In Server name, enter the name of the server where you installed the AdventureWorks sample database. For example, localhost.
In Object Explorer, expand the Databases node.
Right-click on AdventureWorks, and choose New Query.

Paste the following query into the query pane.

SELECT TOP 5 S.OrderDate, S.SalesOrderNumber, S.TotalDue, 
       C.FirstName, C.LastName
FROM    HumanResources.Employee E INNER JOIN
        Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
        Sales.SalesOrderHeader S ON E.EmployeeID = S.SalesPersonID
ORDER BY S.TotalDue DESC
FOR XML AUTO

In the results pane, select the xml result and copy it.

To add a root node to the XML

Open Notepad or the editor of your choice.
Paste the XML text.
Insert the following XML before the first line: <Query><XmlData><Root>.
Add the following XML to the last line: </Root></XmlData></Query>

This is the XML data you can use for test data.

To define a report dataset for the Web service

Start Report Designer and open the report server project created for this tutorial.
In Data view, select New Dataset. Type a name for the dataset (for example, XMLEmbeddedDataSet).
In the Dataset dialog box, in Data source, select New Data Source. The Data Source dialog box appears.
Type a name for the data source (for example, XMLEmbeddedDataSource).
In Type, select XML.
Leave Connection string blank.
Click OK to save your changes and close the Data Source dialog box.
Click OK and close the Dataset dialog box.

From Notepad or the editor you used in the previous procedure, copy the query text, and then paste it in the Query pane. The query should look like the following text:

<Query>
<XmlData>
<Root>
<S OrderDate="2003-07-01T00:00:00" SalesOrderNumber="SO51131" TotalDue="247913.9138">
  <C FirstName="Shu" LastName="Ito" />
</S>
<S OrderDate="2003-10-01T00:00:00" SalesOrderNumber="SO55282" TotalDue="227737.7215">
  <C FirstName="Shu" LastName="Ito" />
</S>
<S OrderDate="2002-07-01T00:00:00" SalesOrderNumber="SO46616" TotalDue="207058.3754">
  <C FirstName="Jae" LastName="Pak" />
</S>
<S OrderDate="2002-08-01T00:00:00" SalesOrderNumber="SO46981" TotalDue="201490.4144">
  <C FirstName="Ranjit" LastName="Varkey Chudukatil" />
</S>
<S OrderDate="2002-09-01T00:00:00" SalesOrderNumber="SO47395" TotalDue="198628.3054">
  <C FirstName="Michael" LastName="Blythe" />
</S>
</Root>
</XmlData>
</Query>

Click Run (!) to view the result set. Notice that the default query for XML selects all the attributes and elements on the first path to a leaf node.
Specify which elements to include in the result set by including an ElementPath tag in the query. Paste the following text in the Query pane after the Query tag but before the XmlData tag.
```
<ElementPath>Root /S  {@OrderDate (Date), @TotalDue (Decimal)} /C {@LastName} </ElementPath>
```
This element path specifies the following:
- For the S element, return the OrderDate attribute as a Date type and the TotalDue attribute as a Decimal type.
- For the C element, return the LastName attribute (default is String type).
Click Run (!) to view the result set. The results now include just those elements and attributes you've specified in the ElementPath section. The OrderDate attribute specifies a Date type to convert its value to DateTime. Similarly, TotalDue converts to a Decimal format.
Click the Refresh Fields () button on the toolbar. This saves the report definition and updates the view of fields in the Report Datasets window to show all the fields you can use.

Next Steps

You have successfully defined a report dataset from XML data embedded in the dataset query. When the report is processed, data from each XML Table element and its attributes are retrieved from the Web service. Next, you will create a report that uses the datasets in Lessons 1, 2, and 3. See Lesson 4: Creating a Report that Uses XML Data.

Tasks

Reference

Concepts

Other Resources

Help and Information