sql server - (SSRS) - Extracting <AutoRefresh> Value from [ReportServer].[dbo].[Catalog] with XML -

i'd extract value (as int) <autorefresh>0</autorefresh> [reportserver].[dbo].[catalog] table. know can string manipulation substring/charindex, i'd ideally extract using proper xml.

an example of xml (once it's been converted) can found here:

http://pastebin.com/s1vww9bt

can help?

here's code column varbinary xml:

select top 13 c.itemid     ,c.name [reportname]     ,convert(xml, convert(varchar(max), convert(varbinary(max), c.content)))            [reportdeffinitionxml] [reportserver].[dbo].[catalog] c (nolock) c.type = 2 

the solution given ms employee shrek li @ below link:

https://social.msdn.microsoft.com/forums/sqlserver/en-us/f948ffee-8a3e-4717-b7f7-52e0f280fd4e/ssrs-extracting-autorefresh-value-from-reportserverdbocatalog-with-xml?forum=sqlreportingservices

please note relevant xml scheme needs referenced:

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'),cte as(select    top 50    c.itemid,    c.name [reportname],    convert(xml, convert(varchar(max), convert(varbinary(max), c.content))) [reportdeffinitionxml] [reportserver].[dbo].[catalog] c    c.type = 2) select itemid, [reportname], [reportdeffinitionxml], reportdeffinitionxml].value('(/report/autorefresh)[1]', 'int' ) [autorefreshvalue] cte