June 14, 2016 at 10:38 am
to parse reports metadata from 1000+ reports
I modified code to get additional info about fields and input parameters
WITH
XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd
)
SELECT
name
,
x.value('CommandType[1]', 'VARCHAR(8000)') AS CommandType,
x.value('CommandText[1]','VARCHAR(8000)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(8000)') AS DataSource,
q.value('@Name','VARCHAR(8000)') AS DataSetName,
replace(q.value('Fields[1]','varchar(250)'),'System.String', ',') as fields,
x.value('QueryParameters[1]','varchar(250)') as InputParams
FROM
(
SELECT
name,
CAST
(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM
ReportServer.dbo.Catalog
WHERE
content IS NOT NULL
AND
TYPE != 3 ) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') d(q)
CROSS APPLY q.nodes('Query') r(x)
WHERE
q.value('CommandType[1]', 'VARCHAR(8000)') IS NULL
--= 'CommandText'
and name ='my DBA report example'
ORDER BY
name
if I don't use "replace" function fields and InputParams columns return following strings
fields
ipNetNameSystem.StringisClusterSystem.StringdaySystem.StringcaptureDateSystem.StringstartHourSystem.ByteendHourSystem.ByteavgCPUSystem.DecimalmedianCPUSystem.DecimalmaxCPUSystem.Decimal
InputParams
=Parameters!ipNetName.Value=Parameters!startDate.Value=Parameters!endDate.Value=Parameters!startHour.Value=Parameters!endHour.Value=Parameters!excludeWeekendsFlag.Value
bellow is format of XML report
<DataSet Name="DataSet1">
<Fields>
<Field Name="ipNetName">
<DataField>ipNetName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="isCluster">
<DataField>isCluster</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="day">
and
Query>
<DataSourceName>ds_Perfmon</DataSourceName>
<CommandType>StoredProcedure</CommandType>
<CommandText>getDailyHourRangeCpu</CommandText>
<QueryParameters>
<QueryParameter Name="@ipNetName">
<Value>=Parameters!ipNetName.Value</Value>
</QueryParameter>
<QueryParameter Name="@startDate">
<Value>=Parameters!startDate.Value</Value>
Question : how to modify code in query without using "replace" function return only fields and inputparam names ?
example for fields
for
ipNetName, isCluster,day ,captureDate
and for input params
ipNetName, startDate, endDate
June 14, 2016 at 11:04 am
I queried your sample Fields XML. It seems pretty straightforward.
I took out the rs namespace from the sample, and added a root so it would be well-formed, but the query is what actually matters.
Does this get you what you need?
DECLARE @XML XML = '
<Whatever>
<DataSet Name="DataSet1">
<Fields>
<Field Name="ipNetName">
<DataField>ipNetName</DataField>
<TypeName>System.String</TypeName>
</Field>
<Field Name="isCluster">
<DataField>isCluster</DataField>
<TypeName>System.String</TypeName>
</Field>
<Field Name="day">
<DataField>isCluster</DataField>
<TypeName>System.String</TypeName>
</Field>
</Fields>
</DataSet>
</Whatever>';
SELECT X.Field.query('.').value('(/Field/@Name)[1]','VARCHAR(MAX)')
FROM @XML.nodes('Whatever/DataSet/Fields/Field') AS X(Field);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2016 at 11:14 am
The query below will give you the field names in a column and the data type in a separate column.
WITH
XMLNAMESPACES
(
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS RepDef
)
SELECT
ReportName = Name
,DataSetName = R.value('@Name', 'VARCHAR(8000)')
,FieldName = df.value('(DataField)[1]', 'varchar(250)')
,FieldType = df.value('(RepDef:TypeName)[1]', 'varchar(250)')
FROM
(SELECT
Name
,CAST
(CAST(Content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM
ReportServer.dbo.Catalog
WHERE
Content IS NOT NULL
AND Type != 3
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') Ds(R)
CROSS APPLY R.nodes('Fields/Field') f(df)
ORDER BY
Name;
June 14, 2016 at 11:51 am
thank you, both queries return fields name, but in row format
ipNetName
isCluster
day
but I need output in following format (since I will run it for all reports)
name CommandTypeCommandTextDataSetNamefieldsiputparms
---- ----------- ----------- ---------- ------------------ -------
Report1 StoredProceduresp_getSomedata1DataSet1 Server, db, host Day ipNetName, startDate,endDate
Report2 StoredProceduresp_getSomedata2DataSet1 Cpu,Memeory,AVG_Cpu ipNetName, myparam1,endDate
..
Report333 StoredProceduresp_getSomedata3DataSet1 ipNetNameS,isCluster,version ipNetName, startDate,myparam5
June 14, 2016 at 12:08 pm
There's an easy way to use For XML Path to turn a set of rows into a list. For some reason, this site isn't letting me post a sample of it. Blows up every time I click "Post Reply".
Or, judging by what's happening when I try to link to other sites with samples, it may be either my computer or something in my employers browsing rules or something of that sort.
Search "for xml path" and look for articles about turning data into strings or lists. There are lots of good walk-throughs available.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 15, 2016 at 7:55 am
Thanks a lot
I used you solution with row based presentation of fields
query was ported to excel and it was much easier to present it when I grouped result in pivot table
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply