Set Multiple XMLNAMESPACES to account for different versions of SSRS/SQL in an SQL Query

user_13425868 0
Viewed 100

I am trying to query an SSRS Report Server Database searching for any report that uses a specific stored procedure using LIKE statement.

Code below works perfectly:

WITH XMLNAMESPACES 
( DEFAULT 
  'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition'
, 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS ReportDefinition )
SELECT  
CATDATA.Name AS ReportName
,CATDATA.Path AS ReportPathLocation
,xmlcolumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName  
,xmlcolumn.value('(Query/DataSourceName)[1]','VARCHAR(250)') AS DataSoureName 
,xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(2500)') AS CommandText
FROM (  
    SELECT C.Name
    ,C.Path
    ,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
    FROM  ReportServer.dbo.Catalog C
    WHERE  C.Content is not null
    AND  C.Type = 2
    ) CATDATA
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') xmltable ( xmlcolumn )
WHERE 
xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(500)') LIKE '%sp_%'
ORDER BY CATDATA.Name

However I want to query across multiple XML namespaces to account for the changes in SSRS/SQL versions over time to ensure the query doesn't miss any records.

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

I am still a relative beginner with XML, any ideas or advice how I would go about doing this?

Many thanks

xml-namespaces reporting-services xml sql-server
1 Answers

If you are not bothered about nodes in different namespaces clashing, then you can just ignore the namespaces altogether, by using *:node

SELECT  
CATDATA.Name AS ReportName
,CATDATA.Path AS ReportPathLocation
,xmlcolumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName  
,xmlcolumn.value('(*:Query/*:DataSourceName/text())[1]','VARCHAR(250)') AS DataSoureName 
,xmlcolumn.value('(*:Query/*:CommandText/text())[1]','VARCHAR(2500)') AS CommandText
FROM (  
    SELECT C.Name
    ,C.Path
    ,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
    FROM  ReportServer.dbo.Catalog C
    WHERE  C.Content is not null
    AND  C.Type = 2
    ) CATDATA
CROSS APPLY reportXML.nodes('/*:Report/*:DataSets/*:DataSet') xmltable ( xmlcolumn )
WHERE 
xmlcolumn.value('(*:Query/*:CommandText/text())[1]','VARCHAR(500)') LIKE '%sp_%'
ORDER BY CATDATA.Name

You should always use /text() to get the inner text of a node, for performance reasons.

Note that you can merge your WHERE filter into the nodes filter and do it directly in XQuery:

CROSS APPLY reportXML.nodes(
    '/*:Report/*:DataSets/*:DataSet[
                             *:Query[*:CommandText[
                                   contains(text()[1], "sp_")
                   ]]]') xmltable ( xmlcolumn )
user_148689970
Related