how to query XML to allow SSRS 2008 usage analysis

Question

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.

Answer 1

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?

Answer 2

0

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

answered May 19 '10 at 07:36 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Answer 3

0

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

answered May 12 '10 at 02:09 PM

Bhaskar
333 ● 15 ● 16 ● 20

(comments are locked)

how to query XML to allow SSRS 2008 usage analysis

Follow this question

Related Questions