4

I'm trying to create a Microsoft SQL Report in Visual Studio 2012. My data input for the report is an XML binary file stored in a database.

To break it down, this is what I would like to do:

  1. Retrieve the XML binary file from the database

  2. Populate the report with the content of the XML

  3. Create a PDF version of the report

Step 1 and 3 are not a problem. But step 2 is. I don't know how to use the XML data in a dataset so I use it in the report.

Is there something fundamental I'm missing?

I have created many reports before by using just data from a database but never from XML.

Thank you.

Improve this question

2 Answers 2

Reset to default
2

If the XML is stored in the DB and you can only access it through a SQL query, I'd suggest the following:

  1. Create an XML datasource with an empty connection string. Normally it'd be a Web site/service URL.
  2. In the report's dataset choose to use the XML data source. Note that the only possible command type is now Text.
  3. Add an internal parameter for the report, which would contain your XML file.
  4. Write an expression to get the default value for the parameter from the DB. The value should be your XML.

  5. In the dataset command use XML Query to select the fields, for example:

    <Query>
   <XmlData>
    <!-- Your XML starts -->
    <Books>
        <Book id='1'>Foo</Book>
        <Book id='2'>Bar</Book>
        <Book id='3'>Baz</Book>
    </Books>
    <!-- Your XML ends -->
   </XmlData>
   <ElementPath>Books/Book</ElementPath>
</Query>
    <!--just tweaked indentation-->

This will add two fields: id and Book. You need to use the parameter instead of the XML.

UPDATE: Sorry for XML formatting, I cannot get code block to work.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for you reply Serge. I'm a little stuck in step 5. I can't get my XML from the input parameter in-bedded in the Query XML. What I want is: <Query><XmlData> <!-- Input Parameter value here --> </XmlData> <ElementPath>Books/Book</ElementPath> </Query> But I just get a syntax error.
Got it to work, just didn't have the correct syntax. ="<Query><XmlData>" & Parameters!ReportParameter1.Value & "</XmlData></Query>"
I was considering this solution as well but I believe it will limit your XML to ~65000 characters due to report parameter length restriction.
0

Only recently I also came across the problem, I did the following.

The parameter solution:

=string.Concat("<Query><XmlData>", Parameters!TypePLateXml.Value, "</XmlData></Query>")
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.