Edit

Share via

Element Path Syntax for XML Report Data (SSRS)

  • Article

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.

Note

Element path syntax is namespace-independent. To use namespaces in an element path, use the XML query syntax that includes an XML ElementPath element described in XML Query Syntax for XML Report Data (SSRS).

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.

Syntax

  
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.

Remarks

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.

Note

XML tags are case-sensitive. When you specify an ElementNode in the element path, you must exactly match the XML tags in the data source.

Term Definition
Element path Defines the sequence of nodes to traverse within the XML document in order to retrieve field data for a dataset with an XML data source.
ElementNode The XML node in the XML document. Nodes are designated by tags and exist in a hierarchical relationship with other nodes. For example, <Customers> is the root element node. <Customer> is a subelement of <Customers>.
XMLName The name of the node. For example, the name of the Customers node is Customers. An XMLName can be prefixed with a namespace identifier to uniquely name every node.
Encoding Indicates that the Value for this element is encoded XML and needs to be decoded and included as a subelement of this element.
FieldList Defines the set of elements and attributes to use to retrieve data.

If not specified, all attributes and subelements are used as fields. If the empty field list is specified ({}), no fields from this node are used.

A FieldList may not contain both a Value and an Element or ElementNode.
Field Specifies the data that is retrieved as a dataset field.
Attribute A name-value pair within the ElementNode. For example, in the element node <Customer ID="1">, ID is an attribute and @ID(Integer) returns "1" as an integer type in the corresponding data field ID.
Value The value of the element. Value can only be used on the last ElementNode in the element path. For example, because <Return> is a leaf node, if you include it at the end of an element path, the value of Return {@} is Chair.
Element The value of the named subelement. For example, Customers {}/Customer {}/LastName retrieves values for only the LastName element.
Type The optional data type to use for the field created from this element.
NamespacePrefix NamespacePrefix is defined in the XML Query element. If no XML Query element exists, namespaces in the XML ElementPath are ignored. If there is an XML Query element, the XML ElementPath has an optional attribute IgnoreNamespaces. If IgnoreNamespaces is true, namespaces in the XML ElementPath and the XML document are ignored. For more information, see XML Query Syntax for XML Report Data (SSRS).

Example - No Namespaces

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.

Note

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.

Example #1: Empty

Order Qty ID FirstName LastName Customer.ID xmlns
Chair 6 1 Bobby Moore 11 https://www.adventure-works.com
Table 1 2 Bobby Moore 11 https://www.adventure-works.com
Sofa 2 8 Crystal Hu 20 https://www.adventure-works.com
EndTables 2 15 Wyatt Diaz 33 https://www.adventure-works.com

Example #2: Customers {}/Customer

FirstName LastName ID
Bobby Moore 11
Crystal Hu 20
Wyatt Diaz 33

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

LastName
Moore
Hu
Diaz

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

Order Qty
Chair 6
Table 1
Sofa 2
EndTables 2

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

Order.ID FirstName LastName ID
1 Bobby Moore 11
2 Bobby Moore 11
8 Crystal Hu 20
15 Wyatt Diaz 33

XML document: Customers.xml

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:

To embed Customers.XML in a query

  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.

Related content