none
How to use an XML as datasource that has . in the node names

    Question

  • Hi all,

    I'm building reports in SSRS on SQL Server 2008 R2 using XML's as datasources for my reports. This because the application that it is for generates XML documents as output. Normally I have no problem loading the generated XML file into a dataset using the <Query><ElementPath>--data--</ElementPath></Query> syntax.

    But now I have run into a problem I do not find a solution to. The generated XML (which I cannot change) uses a dot in the node names. What is the syntax I have to use to get the data out? I do not get errors when I try using it, but I also do not get any data shown.

    For reference, here are some small examples with the XML file included:

    The first one is how I have been doing it so far:

    <ReportDataSet>

         <BAQReportResult>

              <Company>ZZZZ</Company>

         </BAQReportResult>

    </ReportDataSet>

    I get the data out of the XML file just fine using

    <Query>

         <ElementPath>

              ReportDataSet{}/BAQReportResult{Company}

         </ElementPath>

    </Query>

    Now my XML file looks like this (the only change is the . in the node name:

    <ReportDataSet>

         <BAQReportResult>

              <Company.Company>ZZZZ</Company.Company>

         </BAQReportResult>

    </ReportDataSet>

    Now when I try writing a query to create the dataset like this:

    <Query>

         <ElementPath>

              ReportDataSet{}/BAQReportResult{Company.Company}

         </ElementPath>

    </Query>

    I get no errors when looking at the report in Preview, but I also do not get any data back (and there is data in the XML file).

    If anyone could help me out with this issue, I would really appreciate that.

    Wednesday, October 30, 2013 9:41 AM

All replies