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

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

(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

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

yep, can you believe it was a case sensitive issue. thanks. Have posted my used code as separate answer to get formatting right
May 19, 2010 at 07:35 AM Fatherjack ♦♦
ahh yes - xml is case sensitive! How do developers cope?
May 19, 2010 at 09:01 AM Kev Riley ♦♦
(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

Bhaskar gravatar image

Bhaskar
333 16 17 20

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1838
x553
x150

asked: May 12, 2010 at 12:46 PM

Seen: 2237 times

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