Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

SQL Server shred XML with 2 namespaces

How can I retrieve the Stored Procedure name from ReportServer database?

I am trying to get the stored procedure name for about 300+ reports.  Seemed simple enough because  I can get the RDL from the ReportServices database using the query in the snippet..  The 2nd query returns the RDL as XML, but any attempts to shred return blank.  It appears that there are 2 namespaces declared in the root and (best guess) that seems to be part of the problem.

Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">


--------get rdl for each report------------
SELECT ds.[name] as datasource
, c.[name] as report
, CAST(CAST(content AS VARBINARY(max)) AS XML) as rdl
, LEFT( c.path, LEN(c.path) - LEN(c.[name]) ) as folder
INTO #tmp
FROM dbo.Catalog c
INNER JOIN dbo.DataSource ds ON c.ItemId = ds.ItemId 
WHERE ds.[name] IS NOT NULL
ORDER BY datasource, report
 
-------------get stored procedure name----------
SELECT rdl 
, rdl.value( '(/Report/DataSets/DataSet/Query/CommandType)[1]', 'VARCHAR(50)')
, rdl.value( '(/Report/DataSets/DataSet/Query/CommandText)[1]', 'VARCHAR(50)')
FROM #tmp

Open in new window

0
fesnyng
Asked:
fesnyng
  • 2
1 Solution
 
Anthony PerkinsCommented:
As you have discovered you need to add the namespaces, as in:
Adding Namespaces Using WITH XMLNAMESPACES
http://msdn.microsoft.com/en-us/library/ms177400.aspx
0
 
fesnyngAuthor Commented:
PERFECT.   Well, almost.  It leaves me with some more questions -- such as how to identify which elements belong to which namespace. .  But, the query works and those questions can wait for another time.  THANK YOU.
0
 
fesnyngAuthor Commented:
Thank you for your help.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now