Creating Query and Datasets – Xml documents pointed to by a URL for SSRS Reports

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. To retrieve XML data in a report, create a data source with a connection string that specifies a URL to a Web service or an XML document.. You need to know how to create an XML query that retrieves all the data you need.

The Data source and Credentials

  1. Data source type must be XML.
  2. Credentials must be configured for Windows integrated security or no credentials.
  3. Stored and prompted credentials are not supported.
  4. If you select no credentials, Anonymous access is used and it will use the unattended execution account.in SRS.
  5. 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.
  6. Examples: Directly to a document – http://localhost/XML/Customers.xml to Web Service – http://adventure-works.com/results.aspx
    The Query

  1. You must use the SSRS text-based query designer to create the query.
  2. For more information about the generic query designer, see Query Design Tools in Reporting Services.
  3. The query is not analyzed to identify parameters; therefore you must create parameters through the Parameter page on the Dataset Properties dialog box.
  4. The query must return XML data’.
  5. For a Web service, you can provide a <Query> element that specifies a method to call or SOAP action.
  6. 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.

How to Make and XML Query

The Element Path

An element path    <ElementPath> element path </ElementPath>   Specifies the Case Sensitive sequence of nodes to use when retrieving XML data from the data source or how to traverse the XML hierarchical nodes and their attributes in the XML data source, element nodes that have text values and element node attributes become columns in the result set. The values of the nodes and attributes become the row data when you run the query.

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.

To use the default element path, leave the dataset query or the XML ElementPath of the XML Query empty.

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.

Element path syntax is namespace-independent. To use namespaces in an element path, use the XML query syntax that includes an XML ElementPath element. Use the XML Query element to specify namespaces or the IgnoreNamespaces attribute in the XML ElementPath to ignore them.

The following table describes conventions used to define an element path.

Convention

Used for

bold

Text that must be typed exactly as shown.

| (vertical bar)

Separates syntax items. You can choose only one of the items.

[ ] (brackets)

Optional syntax items. Do not type the brackets.

{ } (braces)

Delimits parameters of syntax items.

[,n]

Indicates the preceding item can be repeated n number of times. The occurrences are separated by commas.

 

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. For URL requests, the query parameters are included as standard URL parameters.

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.

Here is some XML, and examples on how to extract data

<?xml version="1.0"?>
<Customers xmlns="http://www.adventure-works.com">
   <Customer ID="11">
      <FirstName>Bobby</FirstName>
      <LastName>Moore</LastName>
      <Orders>
         <Order ID="1" Qty="6">Chair</Order>
         <Order ID="2" Qty="1">Table</Order>
      </Orders>
      <Returns>
         <Return ID="1" Qty="2">Chair</Return>
      </Returns>
   </Customer>
   <Customer ID="20">
      <FirstName>Crystal</FirstName>
      <LastName>Hu</LastName>
      <Orders>
         <Order ID="8" Qty="2">Sofa</Order>
      </Orders>
      <Returns/>
   </Customer>
   <Customer ID="33">
      <FirstName>Wyatt</FirstName>
      <LastName>Diaz</LastName>
      <Orders>
         <Order ID="15" Qty="2">EndTables</Order>
      </Orders>
      <Returns/>
   </Customer>
</Customers>

 

Example

Fields in the dataset

Empty

OrderQtyIDFirstNameLastNameCustomer.IDxmlns
Chair61BobbyMoore11http://www.adventure-works.com
Table12BobbyMoore11http://www.adventure-works.com
EndTables215WyattDiaz33http://www.adventure-works.com

Customers {}/Customer

FirstNameLastNameID
BobbyMoore11
CrystalHu20
WyattDiaz33

Customers {}/Customer {}/LastName

LastName
Moore
Hu
Diaz

Customers {}/Customer {}/Orders/Order {@,@Qty}

OrderQty
Chair6
Table1
Sofa2
EndTables2

Customers {}/Customer/Orders/Order{ @ID(Integer)}

Order.IDFirstNameLastNameID
1BobbyMoore11
2BobbyMoore11
8CrystalHu20
15WyattDiaz33

 

 

NoteNote

When the element path is empty, the query uses the default element path: the first path to a leaf node collection. In the first example, leaving the element path empty is equivalent to specifying the element path /Customers/Customer/Orders/Order. All node value and attributes along the path are returned in the result set, and the node names and attributes names appear as dataset fields.

Advertisements