Any suggestions on how to create report/s in SQL Server Reporting Services using XML data (found in existing table/s)?
views:
57answers:
2
+1
A:
I'm not an SSRS expert but from what I understand I think you might need to create the xml first before and use the XML data source to create your data set. So basically, I'm suggesting a two step process. Export the data to an xml file from Sql Server in step 1 and in Step 2 use the XML data source type to connect to the xml file and generate the SSRS report.
If you directly connect to Sql Server through SSRS, you'll get raw xml data with all the tags and everything and I don't think SSRS will allow you to (programmatically or otherwise) display this raw xml data in proper format.
Sidharth Panwar
2010-09-20 05:14:10
good suggestion. I guess there's no 'shortcut' for this one. (still hoping though)
eibhrum
2010-09-20 05:20:23
Yeah. I'd also like to know if we can merge these two steps somehow :)
Sidharth Panwar
2010-09-20 05:35:38
+1
A:
It's actually easier than you might've originally thought.
- Create a stored procedure that shreds the xml and outputs the underlying info in a nice tabular form.
- Process this data in SSRS using this stored procedure as data source.
Read about xml shredding here: XML Support in Microsoft SQL Server 2005
Denis Valeev
2010-09-20 07:07:31
@eibhrum you mean xml? Xml may not follow strict structure (XSD) it may well be arbitrary. On the other hand, if you define it as xml with XSD structure in your database xpath queries will run faster on such data.
Denis Valeev
2010-09-20 07:33:34
my mistake. i thought it was in wrong format since the xml data occupied one line only.
eibhrum
2010-09-20 08:03:23