x

how to query XML to allow SSRS 2008 usage analysis

I have just started getting to grips with SQL 2008 SSRS and am looking to interrogate the ExecutionLog2 view in the RerportingServices database. This view has a column called AdditionalInfo with a type of XML(.). The datatype in the underlying table is the same. What I would like to know is how to query the values in that column along with other 'normal' columns. Should I be using OPENXML? I have almost 0 experience (read confidence) with XML queries so be gentle with me.

A sample of the data found in that column is

<AdditionalInfo>
<ProcessingEngine>2</ProcessingEngine>
<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>1</Processing>
</ScalabilityTime>
<EstimatedMemoryUsageKB>
<Pagination>6</Pagination>
<Processing>114</Processing>
</EstimatedMemoryUsageKB>
<DataExtension>
<SQL>4</SQL>
</DataExtension>
</AdditionalInfo>

(Sorry for crumby format XML seems to be a bit reluctant to be formatted nicely)

I would like to be able to sum the Pagination and Processing values in the EstimatedMemoryUsageKB tag, along with the corresponding reportpath value in that row.

more ▼

asked May 12, 2010 at 12:46 PM in Default

avatar image

Fatherjack ♦♦
43.7k 79 97 117

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You made me want to know, so I went away to play and came up with this...

drop table kev create table kev(sometextcol varchar(10), somecol xml) insert into kev select'FirstOne','<AdditionalInfo> <ProcessingEngine>2</ProcessingEngine> <ScalabilityTime> <Pagination>0</Pagination> <Processing>1</Processing> </ScalabilityTime> <EstimatedMemoryUsageKB> <Pagination>6</Pagination> <Processing>114</Processing> </EstimatedMemoryUsageKB> <DataExtension> <SQL>4</SQL> </DataExtension> </AdditionalInfo>'

union select'SecondOne','<AdditionalInfo> <ProcessingEngine>2</ProcessingEngine> <ScalabilityTime> <Pagination>0</Pagination> <Processing>1</Processing> </ScalabilityTime> <EstimatedMemoryUsageKB> <Pagination>20</Pagination> <Processing>30</Processing> </EstimatedMemoryUsageKB> <DataExtension> <SQL>4</SQL> </DataExtension> </AdditionalInfo>'

select sometextcol, somecol.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]', 'int') + somecol.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]', 'int')

Does this go some way to helping you?

more ▼

answered May 14, 2010 at 01:44 PM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(comments are locked)
10|1200 characters needed characters left

Dont have much idea, but just thought to know a little bit about using sum with xquery. http://msdn.microsoft.com/en-us/library/ms175504.aspx

more ▼

answered May 12, 2010 at 02:09 PM

avatar image

Bhaskar
333 17 19 24

(comments are locked)
10|1200 characters needed characters left

Actual query used to get average KB required to run report:

SELECT reportpath,
SUM(el.AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]', 'int'))/COUNT(reportpath) AS [avg processing KB]
FROM    [dbo].[ExecutionLog2] AS el
WHERE [el].[ReportPath] != ''
GROUP BY reportpath
ORDER BY 2 desc
more ▼

answered May 19, 2010 at 07:36 AM

avatar image

Fatherjack ♦♦
43.7k 79 97 117

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2071
x647
x185

asked: May 12, 2010 at 12:46 PM

Seen: 2789 times

Last Updated: May 12, 2010 at 04:30 PM

Copyright 2016 Redgate Software. Privacy Policy