Posts Tagged ‘XML Attributes’

h1

SQL SERVER How to Get Stored Procedure Names used by Reports, SQL Reporting Services

02/21/2009

We are using SQL Reporting Services for Reports.

Today we need to identify Stored Procedures used by Reports, so we want to transfer those SPs to Replication server.
I found very good query on Jacob Sebastian Blog.

;WITH xmlnamespaces (
    default
    'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
    NAME AS reportname,
    q.value('@Name[1]', 'VARCHAR(50)') AS datasetname,
    x.value('DataSourceName[1]', 'VARCHAR(50)') AS datasourcename,
    x.value('CommandText[1]', 'VARCHAR(50)') AS spname
FROM
(
    SELECT NAME,
    CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml
    FROM reportserver.dbo.catalog
) a
CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q)
CROSS apply q.nodes('Query') r(x)
h1

How to Read values from XML Variable/Columns with SQL SERVER 2005

12/23/2008

Read Attributes value from XML variable:

DECLARE @xml XML
SELECT @xml = 
'<TejasShah>
  <Att ID="Tejas" Text="TejasShah" />
  <Att ID="Tejas2" Text="TejasShah2" />
  <Att ID="Tejas3" Text="TejasShah3" />
</TejasShah>'

SELECT  x.v.value('@ID[1]', 'VARCHAR(100)') AS [ID],
        x.v.value('@Text[1]', 'VARCHAR(100)') AS [Text]
FROM    @xml.nodes('/TejasShah/Att') x(v)

Output will be:

Read values from xml variable

In other way, we can  also get value from ELEMENTS of xml variable. Lets see:

DECLARE @xml XML
SELECT @xml = '
<TejasShah>
  <Att>
    <ID>Tejas</ID>
    <Text>TejasShah</Text>
  </Att>
  <Att>
    <ID>Tejas2</ID>
    <Text>TejasShah2</Text>
  </Att>
  <Att>
    <ID>Tejas3</ID>
    <Text>TejasShah3</Text>
  </Att>
</TejasShah>'
SELECT  x.v.value('ID[1]', 'VARCHAR(100)'),
        x.v.value('Text[1]', 'VARCHAR(100)')
FROM    @xml.nodes('/TejasShah/Att') x(v)

Output will be the same as above:

Read values from XML Elements