Element Path Syntax for XML Report Data (SSRS)

In Report Designer, you specify the data to use for a report from an XML data source by defining a case-sensitive element path. An element path indicates how to traverse the XML hierarchical nodes and their attributes in the XML data source. To use the default element path, leave the dataset query or the XML ElementPath of the XML Query empty. When data is retrieved from 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. The columns appear as the dataset field collection in the Report Data pane. This topic describes the element path syntax.

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

  
Element path ::=  
    ElementNode[/Element path]  
ElementNode ::=  
    XMLName[(Encoding)][{[FieldList]}]  
XMLName ::=  
    [NamespacePrefix:]XMLLocalName  
Encoding ::=  
        HTMLEncoded | Base64Encoded  
FieldList ::=  
    Field[,FieldList]  
Field ::=  
    Attribute | Value | Element | ElementNode  
Attribute ::=  
        @XMLName[(Type)]  
Value ::=  
        @[(Type)]  
Element ::=  
    XMLName[(Type)]  
Type ::=  
        String | Integer | Boolean | Float | Decimal | Date | XML   
NamespacePrefix ::=  
    Identifier that specifies the namespace.  
XMLLocalName :: =  
    Identifier in the XML tag.   

The following table summarizes element path terms. Examples in the table refer to the example XML document Customers.xml, which is included in the Examples section of this topic.

The following examples use the XML document Customers.xml. This table shows examples of element path syntax and the results of using the element path in a query that defines a dataset, based on the XML document as a data source.

Example #1: Empty

Example #2: Customers {}/Customer

Example #3: Customers {}/Customer {}/LastName

Example #4: Customers {}/Customer {}/Orders/Order {@,@Qty}

Example #5: Customers {}/Customer/Orders/Order{ @ID(Integer)}

To try out the element path examples in the previous section, you can copy this XML and save it to a URL that is accessible by Report Designer, and then use the XML document as an XML data source: for example, https://localhost/Customers.xml.

<?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>  

Alternatively, you can create an XML data source that has no connection string and embed Customers.XML in the query, using the following procedure:

1. Create an XML data source with a blank connection string.

2. Create a new dataset for the XML data source.

3. In the Dataset Properties dialog box, click Query Designer. The text-based query designer dialog box opens.

4. In the query pane, enter the following two lines:

   <Query>

   <XmlData>

5. Copy Customers.XML and paste the text in the query pane after <XmlData>.

6. In the query pane, delete the first line that you copied from Customers.XML: <?xml version="1.0"?>

7. At the end of the query, add the following two lines:

   </XmlData>

   </Query>

8. Click Run Query (!).

   The result set displays 4 lines of data with the following columns: xmlns, Customer.ID, FirstName, LastName, ID, Qty, Order.

9. Select OK.

• XML Connection Type (SSRS)
• Reporting Services Tutorials (SSRS)
• Add, Edit, Refresh Fields in the Report Data Pane (Report Builder and SSRS)