Defining Report Datasets for XML Data

Reporting Services provides a data processing extension that supports report data retrieval from an XML data source. Reports can use data from XML documents and Web services, or embed XML in the query. There is no built-in support for retrieving XML documents from a SQL Server database. A Reporting Services dataset includes a data source, a query, and a collection of fields.

For step-by-step instructions about how to configure the data source and dataset, add fields to the report layout, and create a report that displays the XML data retrieved from an XML Web service, see Tutorial: Using XML Data in a Report.

Connecting to and Retrieving Data from an XML data source

To retrieve XML data in a report, create a data source with a connection string that specifies a URL to a Web service or XML document. You can also create a data source with an empty connection string and embed XML data in the dataset query.

Each XML data source must have the following characteristics:

• Data source type must be XML. Reporting Services provides an XML data processing extension to retrieve and process the data in a report.
• Credentials must be configured for Windows integrated security or no credentials.
  Stored and prompted credentials are not supported. Remember that if you disable Windows integrated security, you cannot use it to retrieve data. If you specify stored or prompted credentials, an error will occur at run time.
  If you select no credentials, Anonymous access is used. Make sure that you have defined the unattended execution account for the report server to connect to an external data source. The XML data processing extension does not pass credentials to the target URL or the Web service; the connection will be unsuccessful unless you have defined the unattended execution account. For more information, see Configuring an Account for Unattended Report Processing.
• The connection string must be a URL that points to the Web service, Web-based application, or XML document available through HTTP. XML documents must have the XML extension. You can also use an empty connection string for XML data embedded in the dataset query.
• The query must return XML data. For XML documents, you can provide an element path using XML syntax similar to XQuery, as described in Element Path Syntax for Specifying XML Report Data. For a Web service, you can provide a <Query> element that specifies a method to call or SOAP action. You can leave the query empty and use the default query if the XML data source has a hierarchical structure that produces the data that you want to use for your report. XML element node values and attributes retrieved when the query runs map to the dataset fields you use in your report.
• You must use the generic query designer to create the query. The query is not analyzed to identify parameters; therefore you must create parameters through the Parameter tab on the Dataset dialog box. For more information about query syntax, see XML Query Syntax for Specifying XML Report Data. For more information about the generic query designer, see Query Design Tools in Reporting Services.

Connection Strings for XML Data Sources

The following examples illustrate the connection string syntax for a Web service and XML document, respectively. The file:// protocol is not supported.

For more information about defining a data source, see How to: Create or Edit a Report-Specific Data Source (Report Designer).

Queries for Datasets with XML Data Sources

A dataset includes a query, which is the command text that runs against a data source to retrieve a specific result set. The result set maps to the collection of fields in a dataset. You can also set filter values on the dataset to limit results returned from the data source. The possible values for a dataset query for a data source that is type XML are shown in the following table.

Dataset query

Description

Empty

Use an empty query to create a default result set. The default query is created by reading the data source and traversing the XML node hierarchy to the first leaf collection. The result set includes all nodes with text values and all node attributes along that path. Columns in the result set are mapped to fields for the dataset.

An element path

Specifies the sequence of nodes to use when retrieving XML data from the data source.

An XML Query element

An XML query specification with the following optional elements.

XML data source

Required XML elements

Optional XML Elements

For a Web service<Method Namespace="namespace" Name="MethodName" /> -- or -- <SoapAction>soap action</SoapAction>``<ElementPath> element path </ElementPath>``<Method Namespace="namespace" Name="MethodName" /> -- or -- <SoapAction>soap action</SoapAction>

For an XML document<ElementPath> element path </ElementPath>

For an embedded XML document<XmlData> inner XML </XmlData><ElementPath> element path </ElementPath> -- or -- <ElementPath IgnoreNamespaces="true"> element path </ElementPath>

Use the XML Query element to specify namespaces or the IgnoreNamespaces attribute in the XML ElementPath to ignore them. For more information about the XML Query element, see XML Query Syntax for Specifying XML Report Data. For more information about element path syntax, see Element Path Syntax for Specifying XML Report Data.

Requirements for Retrieving XML Web Service Data

The XML data processing extension does not detect the schema for you. Therefore, you must have some way of discovering which SOAP methods will retrieve the data that you want. You must also understand the addressing scheme or namespace that the Web service uses for its data.

Requirements for Retrieving XML Document Data

Using the http protocol, the server must return XML data or the XML data must be embedded in the XML Query element. If you refer to an XML document directly using the http protocol, the extension must be .xml. You must know how to create an XML query that retrieves all the data you need. If you do not specify an element path, the default behavior for parsing an XML document is to select the first available path to a leaf-node collection in the XML document. If the XML document includes additional paths to other sibling leaf-node collections, those nodes will be ignored unless you specify a path in your query. For more information, see Element Path Syntax for Specifying XML Report Data.

External Resources

Reporting Services: Using XML and Web Service Data Sources

See Also

Concepts

Connecting to a Data Source

Other Resources

Tutorial: Using XML Data in a Report

Help and Information

Getting SQL Server 2005 Assistance

Change History