My XML skills are poor when querying SSRS

Question

Post reply

My XML skills are poor when querying SSRS

mtassin

SSC-Insane

Points: 23099
More actions
January 24, 2012 at 2:58 pm

#391394

I'm trying to query SSRS to find the data sources for reports based on models
I have this query
;WITH base_bin AS (
SELECT
name,
xmlcontent = CAST(CAST(content AS VARBINARY(MAX)) AS XML),
type
FROM
reportserver.dbo.Catalog
WHERE
type = 2 )
SELECT
a.NAME,
a.xmlcontent.value('/Report/DataSources/DataSource/DataSourceReference','varchar(max)')
FROM
base_bin a
but I get this error...
Msg 2389, Level 16, State 1, Line 13
XQuery [base_bin.xmlcontent.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
I started asking the .Net coders around here what a singleton is and they're also stumped, telling me my query should work. Since I suck at XML I thought I somebody else might have done this.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

Would be helpful to know what the value for content is for a record that is causing the error.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

vinothraj Ten Centuries Points: 1349 More actions · Answer 2

Its seems to be multiple "DataSourceReferences" tags are there in the xml content which its failed to convert the value to Varchar(max).

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 3

Try changing

a.xmlcontent.value('/Report/DataSources/DataSource/DataSourceReference','varchar(max)')

to

a.xmlcontent.value('/Report/DataSources/DataSource/DataSourceReference[1]','varchar(max)')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

mtassin SSC-Insane Points: 23099 More actions · Answer 4

For those looking for sample content. This is straight out of SSRS's database... If you have an SSRS instance, it's available to you....

<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">

<AutoRefresh>0</AutoRefresh>

<DataSources>

<DataSource Name="DataSource1">

<DataSourceReference>http://ReportServer/Reporting/Models/Implementation/Implementation Report Model.smdl</DataSourceReference>

<rd:SecurityType>None</rd:SecurityType>

<rd:DataSourceID>01bb3f22-2f9d-4687-98d6-8f572bb2997b</rd:DataSourceID>

</DataSource>

</DataSources>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<DataSourceName>DataSource1</DataSourceName>

<CommandText><SemanticQuery xmlns="http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder" xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign">

<Hierarchies>

<Hierarchy>

<BaseEntity>

<EntityID>G5a1b6069-7353-45f4-af4f-f9231b38b832</EntityID>

</BaseEntity>

<Groupings>

<Grouping Name="TargetSystem">

<Expression Name="TargetSystem">

<AttributeRef>

<AttributeID>Gd057e7ca-1117-4587-aeca-941b17348816</AttributeID>

</AttributeRef>

</Expression>

</Grouping>

</Groupings>

<Filter>

<Expression Name="filter">

<Literal>

<DataType>Boolean</DataType>

<Value>true</Value>

</Literal>

<CustomProperties>

<CustomProperty Name="qd:Filter" />

<CustomProperty Name="qd:ContextEntityID">

<Value xsi:type="xsd:string">G5a1b6069-7353-45f4-af4f-f9231b38b832</Value>

</CustomProperty>

<CustomProperty Name="qd:AutoChangeBaseEntity" />

<CustomProperty Name="qd:Design">

<Value xsi:type="xsd:string">expr1</Value>

</CustomProperty>

</CustomProperties>

</Expression>

</Filter>

</Hierarchy>

</Hierarchies>

</SemanticQuery></CommandText>

</Query>

<Fields>

<Field Name="TargetSystem">

<DataField>TargetSystem</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

</Fields>

</DataSet>

</DataSets>

<ReportSections>

<ReportSection>

<Body>

<ReportItems>

<Tablix Name="Tablix1">

<TablixBody>

<TablixColumns>

<TablixColumn>

<Width>1.00347in</Width>

</TablixColumn>

</TablixColumns>

<TablixRows>

<TablixRow>

<Height>0.34375in</Height>

<TablixCells>

<TablixCell>

<CellContents>

<Subreport Name="Subreport1">

<ReportName>http://ReportServer/Test/ImplPieTest.rdl</ReportName>

<Parameters>

<Parameter Name="TargetSystem">

<Value>=Fields!TargetSystem.Value</Value>

</Parameter>

</Parameters>

<Style>

<Border>

<Style>None</Style>

</Border>

</Style>

</Subreport>

</CellContents>

</TablixCell>

</TablixCells>

</TablixRow>

</TablixRows>

</TablixBody>

<TablixColumnHierarchy>

<TablixMembers>

<TablixMember />

</TablixMembers>

</TablixColumnHierarchy>

<TablixRowHierarchy>

<TablixMembers>

<TablixMember>

<Group Name="Details" />

</TablixMember>

</TablixMembers>

</TablixRowHierarchy>

<DataSetName>DataSet1</DataSetName>

<Height>0.34375in</Height>

<Width>1.00347in</Width>

<Style>

<Border>

<Style>None</Style>

</Border>

</Style>

</Tablix>

</ReportItems>

<Height>3.98958in</Height>

<Style>

<Border>

<Style>None</Style>

</Border>

</Style>

</Body>

<Width>6.12847in</Width>

<Page>

<LeftMargin>1in</LeftMargin>

<RightMargin>1in</RightMargin>

<TopMargin>1in</TopMargin>

<BottomMargin>1in</BottomMargin>

<Style />

</Page>

</ReportSection>

</ReportSections>

<rd:ReportUnitType>Inch</rd:ReportUnitType>

<rd:ReportServerUrl>http://ReportServer</rd:ReportServerUrl>

<rd:ReportID>9fc370f5-42c0-48d9-9a74-e32cb1981543</rd:ReportID>

</Report>

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

mtassin SSC-Insane Points: 23099 More actions · Answer 5

However, the main part of the issue appears to have been to do with Namespaces.

;WITH XMLNAMESPACES('http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,

'http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS cl,

DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition')

, base_bin AS (

SELECT

itemid,

creationdate,

modifieddate,

name,

xmlcontent = CAST(CAST(content AS VARBINARY(MAX)) AS XML),

type,

createdbyid,

modifiedbyid

FROM

reportserver.dbo.Catalog

WHERE

type = 2 )

SELECT

a.NAME,

a.creationdate,

creatorname = creator.Username,

a.modifieddate,

modifiername = modifier.username,

a.xmlcontent.value('(/Report/DataSources/DataSource/DataSourceReference)[1]','varchar(max)')

FROM

base_bin a JOIN users creator ON

a.createdbyid = creator.UserID

JOIN users modifier ON

a.modifiedbyid = modifier.userid

ORDER BY creationdate desc

Which a clever developer helped me come up with late yesterday after I posted this, works.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]