Selecting attributes from SSRS XML data source

Question

I have the folloing XML file:

    <?xml version="1.0" encoding="utf-8"?>
    <root>
          <NB>
            <Company>Company 1</Company>
            <Rank Indicator="increasing">2</Rank>
            <Position Indicator="decreasing">1</Rank>
          </NB>
          <NB>
            <Company>Company 2</Company>
            <Rank Indicator="decreasing">42</Rank>
            <Position Indicator="increasing">6</Rank>
          </NB>
    </root>

What I need to do is create a dataset which outputs the following columns:

• Company
• RankIndicator
• PositionIndicator

I tried something like this:

    <Query>
        <ElementPath>root/ {}/NB</ElementPath>
    </Query>

However, this does not include any attributes. I managed to get one attribute by using something like this:

    <Query>
        <ElementPath>root/ {}/NB/Rank{@Indicator}</ElementPath>
    </Query>

However, I need both Rank and Position indicators and I'm not sure how to provide a list of attributes in this case. Any help would be greatly appreciated. Thanks.

Answer 1

If I understood your comment, you must create a data source with no connection string and a new dataset with your XML content as the query string.

This is what you should do:

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:<Query><XmlData>
5. Copy your XML file and paste the text in the query pane after <XmlData>.
6. Be sure to remove <?xml version="1.0"?>
7. At the end of the query, add the following: </XmlData></Query>
8. Click Run Query (!).

In your case your code would be like this:

<Query>
    <XmlData>
        <root>
            <NB>
                <Company>Company 1</Company>
                <Rank Indicator="increasing">2</Rank>
                <Position Indicator="decreasing">1</Rank>
            </NB>
            <NB>
                <Company>Company 2</Company>
                <Rank Indicator="decreasing">42</Rank>
                <Position Indicator="increasing">6</Rank>
            </NB>
        </root>
    </XmlData>
</Query>

If you want to specify parameters:

    </XmlData>
    <ElementPath>YourField {@}</ElementPath>
</Query>

EDIT

Based on your comment, to retrieve specific fields:

{FieldA, FieldB, FieldC}

Sources:

http://msdn.microsoft.com/en-us/library/ms365158.aspx

http://msdn.microsoft.com/en-us/library/ms345251.aspx