SQL Server shred XML with 2 namespaces

Posted on 2009-04-16
Last Modified: 2012-05-06
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="" xmlns:rd="">

--------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 


ORDER BY datasource, report

-------------get stored procedure name----------


, 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

Question by:fesnyng
    LVL 75

    Accepted Solution

    As you have discovered you need to add the namespaces, as in:
    Adding Namespaces Using WITH XMLNAMESPACES
    LVL 5

    Author Comment

    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.
    LVL 5

    Author Closing Comment

    Thank you for your help.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now