Shredding XML in an SQL column returned to a report. 1

[Peager]

I have a data source to an MS SQL data base that returns an XML datatype column along with a number of other columns I'm using in my report. Is there an elegent way to shred this column with Reporting Services or will I best shred it on the SQL side and return it in a separate data set?

Best regards all,

Paul

 

[xlbo]

shred it in the query

2008 does have the ability to display html markup but I don't think it extends to xml - you can use xml data sources but from the sounds of it you're best off using a stored proc to get your data together into a simple dataset (or 2)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Peager]

Geoff,

Thank you for your response. I knew I could get there that way but I found some 2005 MS documentation that led me to believe it could be done the other way and, with painfull persaverance, I found that it could!

First of all, the somewhat helpful docs:

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

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

The second document above was written for SSRS 2005 and walks throught several examples. The final example provided the tricks I needed.

I first started with an outer report based on a stored procedure that returned a number if fields for the report detail records. One of those fields was an XML data type.

I then turned to writing a sub-report that would provide additional line detail to the outer report. The steps as follows:

1) I added a text parameter field to be used to pass in the XML data I wished the sub-report to process. I set a default for this parameter to hold some data to be used as I was developing the report.

<segments>
  <segment>
    <seg>1</seg>
    <sn>51407</sn>
    <wc>POL</wc>
    <len>70</len>
    <class>H2</class>
    <top></top>
  </segment>
  <segment>
    <seg>2</seg>
    <wc>FW</wc>
    <len>15</len>
  </segment>
</segments>

2) I added an embedded XML Data source. (I would expect this could be set up as a shared data source but I haven't tested that yet.) I just named it XMLField and selected 'XML' as the connection type. I left the connection string blank.

3) I added a Data Set on the XML Data Source I created in step 2. I manually defined the columns that would appear in the XML. THIS IS CRITICAL! I added a Data Set Parameter and linked it to the report parameter I created in step 1. I left the query type as Text and saved the Data Set. Re-opening the Data Set shows that the Query was magicly populated:

=<Query>
<XmlData>
Parameters!XmlData.Value
</XmlData>
</Query>

4) The problem was that this was not quite correct and the report would not save giving "[BC30201] Expression expected" errors. After fiddling with it I found the following did work:

="<Query><XmlData>"+
Parameters!XmlData.Value
+"</XmlData></Query>"

5) I saved the report and went back and added it to the parent report, linking the XmlData report parameter to the column in the outer report containing the XML data and it worked!

I am embarassed to say how long it took to resolve and hope this helps someone else in the future.

Paul

 

[xlbo]

Thank you for sharing the solution - it would appear that this is not a commonly understood piece of functionality in SSRS

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question