I am currently playing around with the SQL Server Reporting Services to create a number of custom reports for our servicedesk.Most of the things I want to pull from the database I can get without issue,however I am strugling with the customFields column in the kasadmin.SDIncident table.Never worked with the XML datatype before Anybody have any suggestions (or an example query) on how to break down the data stored in that column and pull a specific field (like eg <Field fieldName="Source">Call</Field>) from it with only the value (no XML tags).
In SSRS create a place holder. Choose the properties of the place holder and on the general tab check the radio button that says, "HTML - Interperet HTML tags as styles". I've done this before with HTML and it worked nicely. I haven't tried XML before but it's worth a try.
Never got round to figuring this one out untill recently.
Didn't put in the effort to look for an elegant solution on the XML side, so I decided to just CAST the field into a VARCHAR(MAX) and use normal string handling to pick out the data I need.
substring(cast(ic.customFields as varchar(max)), (charindex('Onsite', cast(ic.customFields as varchar(max))) + 8 ), 4) as Onsite
To get acceptable text in the report I just use expressions inside the report to clean it up.
=Replace(Replace(Fields!Onsite.Value, "true", "Y"), "fals", "N")
If anybody has a cleaner way of handling this, please let me know, I am always interested.