XML Query Syntax for Specifying XML Report Data

  • Article

In Reporting Services, you can create datasets for XML data sources. After you define a data source, you create a query for the dataset. Depending on the type of XML data pointed to by the data source, you create the dataset query by including an XML Query or an element path. An XML Query starts with a <Query> tag and includes namespaces and XML elements that vary depending on the data source. An element path is namespace-independent and specifies which nodes and node attributes to use from the underlying XML data with an XPath-like syntax. For more information about element paths, see Element Path Syntax for Specifying XML Report Data.

You can create an XML data source for the following types of XML data:

  • Xml documents pointed to by a URL using http protocol
  • Web service endpoints that return XML data
  • Embedded XML data

How you specify an XML Query or an element path depends on the type of XML data.

For an XML document, the XML Query is optional. If it is included, it can contain an optional XML ElementPath. The value of the XML ElementPath uses the element path syntax. You include the XML Query and XML ElementPath to process namespaces correctly when needed by the XML data from the data source.

For a Web service endpoint pointed to by a connection string URL, the XML Query defines either the Web service method, the SOAP action, or both. The XML data provider creates a Web service request that retrieves XML data to use for the report.

Note

When a Web service namespace includes a forward slash (/) character, include both the Web service method and the SOAP action so that the XML data processing extension can derive the namespace correctly.

For an embedded XML document, the XML Query defines the embedded XML data to use, includes optional namespaces, and contains an optional XML ElementPath..

Specifying Query Parameters for XML Data

You can specify query parameters for XML documents.

  • For URL requests, the query parameters are included as standard URL parameters.
  • For Web service requests, query parameters are passed to the Web service method. To define a query parameter, use the Parameters tab of the Dataset dialog box. For more information, see Dataset (Parameters Tab, Report Designer).

Example

The examples in the following table illustrate how to retrieve data from the Report Server Web service, an XML document, and embedded XML data.

XML data source Query example

Web service XML data from ListChildren method.

<Query>

<Method Name="ListChildren" Namespace="https://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices" />

</Query>

Web service XML data from SoapAction.

<Query xmlns=namespace>

<SoapAction>http://schemas/microsoft.com/sqlserver/2005/03/23/reporting/reportingservices/ListChildren</SoapAction>

</Query>

XML Document or embedded XML data using namespaces.

Query element specifying namespaces for an element path.

<Query xmlns:es="https://schemas.microsoft.com/StandardSchemas/ExtendedSales">

<ElementPath>/Customers/Customer/Orders/Order/es:LineItems/es:LineItem</ElementPath>

</Query>

Embedded XML document.

<Query>

<XmlData>

<Customers>

<Customer ID="1">Bobby</Customer>

</Customers>

</XmlData>

<ElementPath>Customer {@}</ElementPath>

</Query>

XML document using default.

No query.

The element path is derived from the XML document itself and is namespace-independent.

Note

The first Web service example lists the contents of the report server using the ListChildren method. To run this query, you must create a new data source and set the connection string to https://localhost/reportserver/reportservice2005.asmx. The ListChildren method takes two parameters: Item and Recursive. Set the default value for Item to / and Recursive to 1.

Specifying Namespaces

Use the XML Query element to specify the namespaces used in the XML data from the data source. The following XML query uses the namespace sales. The XML ElementPath nodes for sales:LineItems and sales:LineItem use the namespace sales.

<Query xmlns:sales=
"https://schemas.microsoft.com/StandardSchemas/ExtendedSales">
   <SoapAction>
      https://schemas.microsoft.com/SalesWebService/ListOrders 
   </SoapAction>
   <ElementPath>
      Customers/Customer/Orders/Order/sales:LineItems/sales:LineItem
   </ElementPath>
</Query>

To specify the data provider namespace so that the default namespace remains empty, use xmldp. This is shown in the following example.

Example

The following examples use the XML document DPNamespace.xml, which is provided for illustration after the table. This table shows two examples of XML ElementPath syntax that includes namespace prefixes.

XML Query Element

Resulting fields in the dataset

<Query/>

Node

ns

Value Ahttps://schemas.microsoft.com/...

Value Bhttps://schemas.microsoft.com/...

Value Chttps://schemas.microsoft.com/...

<xmldp:Query xmlns:xmldp="https://schemas.microsoft.com/sqlserver/2005/02/reporting/XmlDPQuery" xmlns:ns="https://schemas.microsoft.com/...">

<xmldp:ElementPath>Root {}/ns:Element2/Node</xmldp:ElementPath>

</xmldp:Query>

Node

Value D

Value E

Value F

XML document: DPNamespace.xml

You can copy this XML and save it to a URL accessible by Report Designer to use as an XML data source: for example, https://localhost/DPNamespace.xml.

<Root xmlns:ns="https://schemas.microsoft.com/...">
   <ns:Element1>
      <Node>Value A</Node>
      <Node>Value B</Node>
      <Node>Value C</Node>
   </ns:Element1>
   <ns:Element2>
      <Node>Value D</Node>
      <Node>Value E</Node>
      <Node>Value F</Node>
   </ns:Element2>
</Root>

See Also

Concepts

Defining Report Datasets for XML Data

Other Resources

Tutorial: Using XML Data in a Report

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Provided examples of XML query syntax.