Lesson 3: Defining a Report Dataset from Embedded XML Data

New: 17 July 2006

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

1. Open Microsoft SQL Server Management Studio.

2. On the Connect to Server dialog box, choose Server typeDatabase Engine.

3. In Server name, enter the name of the server where you installed the AdventureWorks sample database. For example, localhost.

4. In Object Explorer, expand the Databases node.

5. Right-click on AdventureWorks, and choose New Query.

6. 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
   

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

To add a root node to the XML

1. Open Notepad or the editor of your choice.

2. Paste the XML text.

3. Insert the following XML before the first line: <Query><XmlData><Root>.

4. 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

1. Start Report Designer and open the report server project created for this tutorial.

2. In Data view, select New Dataset. Type a name for the dataset (for example, XMLEmbeddedDataSet).

3. In the Dataset dialog box, in Data source, select New Data Source. The Data Source dialog box appears.

4. Type a name for the data source (for example, XMLEmbeddedDataSource).

5. In Type, select XML.

6. Leave Connection string blank.

7. Click OK to save your changes and close the Data Source dialog box.

8. Click OK and close the Dataset dialog box.

9. 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>
   

10. 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.

11. 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).

12. 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.

13. 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.

See Also

Concepts

Reporting Services Tutorials

Other Resources

Defining Report Datasets for XML Data
Connecting to a Data Source
Defining Report Datasets
How to: Create or Edit a Report-Specific Data Source (Report Designer)
How to: Create a Dataset (Report Designer)
Working With Fields in a Report Dataset
How to: Add, Edit, or Delete a Field in the Datasets Window (Report Designer)
Report Datasets (Report Designer)
Element Path Syntax for Specifying XML Report Data

Help and Information

Getting SQL Server 2005 Assistance