Selecting attributes from SSRS XML data source

reporting-services, xml

Solution

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:

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

- Create a new dataset for the XML data source.

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

- In the query pane, enter the following:`<Query><XmlData>`

- Copy your XML file and paste the text in the query pane after `<XmlData>`.

- Be sure to remove `<?xml version="1.0"?>`

- At the end of the query, add the following: `</XmlData></Query>`

- 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

Problem

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.

Original source